Tony Tony - 10 months ago 89
MySQL Question

Asset Inventory Database Design

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 Asset(barcode);
Table Rack(rack_id, rackName);
Table Desk(desk_id, deskName);
Table Storage(storage_id, StorageName);

And for each "location" table (Rack, Desk, Storage) I had a table to keep track of where an asset was placed:

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);

But I didn't like the idea of having three separate tables to keep track of the assets. So I figured I should make a Location table

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);
Table Asset(barcode);

So now the Location table keeps track of where the asset is. But what weirds me out is that if I query the Location table, I have to check Rack, Desk and Storage. Is that the correct way to design this? Appreciate any thoughts or advice.

Answer Source

You should have a location_type table.

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 type

And instead of multiple table for Rack, Desk, Storage a single table location

location_id      location_type_id  name
    1                  1           'Im a Rack'
    2                  1           'Im another Rack'
    3                  2           'Im just a desk'

Finally your Assets table

barcode          location_id
 xxxxxx              1            -- is in first rack
 yyyyyy              2            -- is in the second rack