Database Design Question

[JorgeM]

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.

[pritaeas]

You can create an asset, book, magazine and widget table, where the last three have a foreign key to the asset. A join/union can always retrieve the information you need. Another way is to introduce a link table, connecting the asset with the other three. Technically, it would be possible to have an asset pointing to both, e.g. a magazine giving away a book.

[JorgeM]

ok, thanks for the feedback. I was thinking in the same manner. My only concern going in this direction is regarding the total number of tables that may be created and how to create them. Based on this design, table creation and field configuration is going to have to be done using a web application. I wouldnt be creating a new table manually every time this customer decides to track another type of object.

[pritaeas]

You can of course configure the tables in code. If they don't exist create them. You will need this configuration anyway, to simplify adding new information. I wouldn't create a link table in this particular case, as that would need maintenance too.

I just wonder what the use of the asset table is going to be. Is there some information that all types share, like an internal code or location or something? And what kind of information do you want to extract from the assets?

[JorgeM]

There is some common information between these assets such as location, purchase information, date/time stamps.

I think the idea originally was that many of the searches being performed would be against these common fields. For example, to figure out what the inventory is for a particular location. If its in one main asset table, that would be easy. If I had 30 independent tables, I'd have to create unions...it just seemed complicated.

I havent worked on this type of application before so that's why I'm not sure on the best approach.

[pritaeas]

An update trigger on the book table can still update the inventory in the asset table, so that wouldn't be a problem. I think I would go for separate tables, with a FK back to the asset table (convenient). Am sure some things can be fixed by using triggers, views or stored procedures, so you don't have to everything client-side.

[JorgeM]

ok, thanks for the recommendations.