Hi All,

I've a little problem regarding the design of my product table. I'm currently working on a hardware tracking system, that keeps track of hardware that is assigned to projects.

I need to keep track of the following hardware:
-PCs
-Laptops
-Video game consoles
-Console peripherals
-Monitors (LCDs)

The problem is that if I only have one table with all the different hardware belonging to the categories above, some of the attributes in the table which are specific to one category may need to contain null values for other type of hardware (e.g. monitor_size attribute is not applicable to PCs or video game consoles), is this acceptable from the relation model point of view?

I was thinking to have 5 different product tables, one for each of the categories above, each table with the specific attributes for the type of hardware. Then, have the serial_id as primary key in each of the 5 tables. I'd then have a 6th table called full_products, which only contains the serial_id of all the hardware, name and description. The 5 tables for the hardware categories wouldn't have these two attributes name and desc, but it'd have the same serial_id so that I could join them based on the serial_id. In this way I'd avoid having only one generic product table with some attributes left null.

I have a couple of questions regarding this last option:

is it acceptable from the relational model point of view to have the serial_id of the product duplicated in two tables? e.g. full_products and product_pc_cat.

It'd be harder to maintain as I'd need to update two tables for every new product.

I'd appreciate your opinion and if you have a better design approach for the example above.

Thanks