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?


#3

It’s not that easy to work this out in a way that would be easy to use in Airtable - but here’s an idea which would at least work…

As now, you would have a products table which would contain all the common attributes - for example manufacturer, cost, type etc.

You would then create a Product Properties table which would have the following fields:

  • Product (link to product table)
  • Property
  • Value

(Plus any others you need).

In the Name Column you could have it calculated as Property - Value so that you could see the information displayed in a useful way in the Product Table.

This would be more effective in Expanded view of the records but should be usable.

You could even consider using Zapier or Integromat to automatically generate a set of Property records appropriate for each new product (although of course this wouldn’t be Instant). This could even check for those entered manually and only create additional records needed (may only be possible with Integromat).

Hope this helps.

Julian


#4

Hi

Perhaps something from this thread might help, to create unique ID’s, minus the company ID. Which can then be incorporated into the base:

Just wondering if it could help.

MK