I have a scenario that I am assisting with. I am not sure how to advise in this case. I am hoping that someone with a lot more database design/normalizing experience can clarify this for me.
I am working on a simple database which will contain a table to track inventory. At first, this table was only going to contain one type of asset. Now, there are several types of assets. Each of these types of assets have different attributes.
Would it be appropriate to still have one table for assets with multiple fields to capture these attributes and simply store a value of "NULL" when the attribute is not relevant to the asset?
A simple example to reference for discussion....
Say at first the purpose of the table was to track an inventory of books. Of course, one table is all that is needed. Now, I need to track books, magazines, and widgets. In the future, it could be many more. However, in this example, these three types of assets have different attributes (fields). For example, books have an ISBN number where widgets do not. I need to track color for widgets, not ISBN.
Would I still track these in the same inventory table and just leave the ISBN related field as "NULL" when entering in a widget and vice-versa for books and the color field, or would I have a dedicated inventory table with only the fields that are common for all types, then create dedicated tables for books, magazines, and widgets and just join the fields whenever I need to work with individual types of assets?
The total amount of records for the inventory is expected to be less than 10k.
Considering that in the future, other types of assets will be tracked, the idea of creating a dedicated table for each type of asset that stores only the unique attributes is where I am not clear on whether or not this is the correct approach. In other words, it is possible that the client may want to track thousands of different types of assets.
For this reason a single table seemed appropriate, but having 20-30 fileds with "NULL" data doesnt seem to be a correct approach either.