The best approach to abstract the item in storage database (theoretical question - no code required)

Question

I am struggling to find a better approach on the following topic and would appreciate any advice.

1. Description:

Database purpose - warehouse storage. There are a couple of entities such as:

• t_resource (from what the product made).
• t_product (the final product whichis made from resources).
• t_rack where the items are stored.
• t_item which can be type of product or resource.

2. Problem:

How to abstract resources and product in a way that they can fit into the item and be easily searchable later on.

3. My thoughts and possible solution:

• Introduce into the t_item table column with name "type" which will help to differentiate the id`s of resource and product -> there seems big disadvantages of this approach especially if I would like to join representative table to bring product and resource details.
• Introduce independent tables for the t_rack storage and split into t_rack_res and t_rack_product -> this solution probably will have same problem when there will be need to show all the items from both of the tables. Also later if there will be need to scale the item type, I will fall into the problem of creating another table.
• Merge product and resource into one table - the only reason I don't like this idea is that, practically those two things are different and should be separate also because product should be made from resources and contain it.

Answer 1

I would go with your first suggestion - where Item has a "type" column. In practice the disadvantages are not so bad.

You have modelled Product and Resource as different business entities. They must have distinct attributes and processes. If they did not they would be the same entity and modelled in the same table.

Any real-world process that acts on products can read the Product table and read/ write those values specific to a product. Likewise for any process that affects resources. Most likely the set of product-actions and of resource-actions are quite separate. For example, you can sell a product but not a resource; you can machine resource into product but not the other way around.

Those attributes which are common to both Product and Resource and relevant to them being Items (defined as whatever can be put on a Rack), e.g. weight, size, are stored as columns in Item. Once again, processes concerned with manipulation of Items (putting on a rack, taking from a rack, observing load limits etc.) need know nothing of the product (unit price, marketing name, minimum order quantity) or the resource (supplier address, reorder lead time).

For those rare occasions where attributes from Item and from Product/ Resource are needed together the sub-types can be OUTER JOINed to Item.

Were you to split Rack you would, indeed, have the same issues only more so. It is unlikely a rack for holding Product differs in any significant way from a rack holding Resource. These to would actually have the same attributes and allow for the same processes so should be modelled as a single entity and, hence, table.

Merging Product and Resource into one is also viable. More so if there are few distinct attributes. If there are many differences then the system needs to know which columns are populated for which type and which columns must not be, and you're back to two separate processing regimes only glued together and with lots of NULLs.