I work in an IT shop and am trying to create a database to track our hardware inventory. The gist is, a barcoded asset can only be in one location, either a desk, in a rack, or in some kind of storage (on a shelf, pallet, etc).
I originally had something like this.
Table Rack(rack_id, rackName);
Table Desk(desk_id, deskName);
Table Storage(storage_id, StorageName);
Table Desk_Item (desk_item_id, desk_id, barcode);
Table Rack_Item (rack_item_id, rack_id, barcode);
Table Storage_Item (storage_item_id, storage_id, barcode);
Table Location(location_id, barcode);
Table Rack(rack_id, rackName, location_id);
Table Desk(desk_id, deskName, location_id);
Table Storage(storage_id, StorageName, location_id);
You should have a
location_type_id location_type 1 Rack 2 Desk 3 Storage
This also have the advantage if you later want add another
location_type like ex:
'truck' you dont have to modify the db, just add a new
And instead of multiple table for
Storage a single table
location_id location_type_id name 1 1 'Im a Rack' 2 1 'Im another Rack' 3 2 'Im just a desk'
barcode location_id xxxxxx 1 -- is in first rack yyyyyy 2 -- is in the second rack