I am creating a simple internal asset tracking app for my company. Currently we do this through excel (terrible). I'm breaking this down to:
Asset/Item - The tool, hardware, part etc. Site - The overall location it's at: Facility A, Site Location B Bin - A sublocation an asset is located, this could be a building, a toolbox, a crate.
The Bin is causing me problems because an Item can be location at a Site without a Bin, or a Bin can be located inside another Bin, I am not yet sure the best way to structure this database.
I have tried having multiple foreign keys but that gets messy or atleast feels so. I have also tried using the alternatives to Django's GenericForeign Keys as listed here I have used them before with subtly different apps but the self referencing is confusing me. In the end I want to be able to gather a list of items at a site and then have indented lines for each toolbox and the items inside, is there a good method for setting up the models so this can happen?
There are two points of view you might want to consider: 1. whether the model is easy to read, non redundant and consistent with your problem, 2. what kind of queries you are ging to run on the model.
You have already thought about the first aspect. regarding the second I believe some of the queries you want to execute are: 1. listing the assets and link their location. 2. count the assets at a location 3. navigate the location hierarchy and list all the assets at each location
The above suggest that you create a single model for the locations that includes buildings, bins, etc. and differentiate them through a field location_type that could be foreign key to a dictionary table (id, name). then you would need a self referencing foreign_key to the parent location, to create the hierarchy.
This way you would have a single foreign key to the location from the asset and given the location you could query asset_set directly. Any other model would be less transparent. Any difference can be addressed using @property methods, e.g. to save a field that is specific for building you can create a method that first checks if the type is correct and then saves the value into a dynamic field like char_field_1
A variation of the above. If the attributes for Bin, Building etc. are too many and different to be consolidated in one table. Then you can create separate models for each and have a nullable one-to-one field from Location to each. On save you can check that no more than one has been populated.