How to model product details of different types


#1

We’re building an inventory database to store IT hardware information. The devices are workstations, laptops, switches, routers, mobile phones, servers, server parts and so on.

We currently have these tables (and others):

  • Products (a product that exists in the marketplace. These may or may not be an asset. Examples: iPhone 6S, IBM Server XYZ, Macbook Pro 15”)
  • Assets (a product that we’ve received in the building and need to track. These would have asset tags, location, etc)

Each product has similar parent info (manufacturer, model #, product type). However, our issue is that different products have different detail level data. For example a processor would have GHz rating, while a power supply would have MaxWattage and a tablet would have ScreenSize. We want an elegant way of representing this data without putting all of the unrelated data in the “products” table. We tried creating ProductDetails tables for each and every type but we have a ton of details pages and it seems there should be a better way.

In other products we’ve used we relied on some unique primary key items to tie together parent/child but that isn’t in this product. However we really like the product & want to use it.

Any suggestions?