Design Question

I have a design question that I hope this is the appropriate forum to ask the question.

I have to create a set of tables that mirror one of my companies business practices. I am not sure of the best strategy.

My company makes serveral products. Each of these products are made up of a different number of fields. For instance, the product paper has 10 fields that my company uses to describe paper, while the product ink only has two fields. I am not really sure how to design this. I don't want to create a Products table with 10 fields because we might someday sell a product that has 20 fields. I don't want to create a PaperProducts table and an InkProducts table. That could lead to hundreds of tables. Is there a good way to design this?

Thanks

If you use sophisticated column-awareness+dynamic-querying strategies, it's possible to use any number of different tables and have the application handle it appropriately. I've had success doing this, but requires that the application developer be very good at their job

Alternatively, you can have a single "Products" table with the most basic information, and a separate "ProductValues" table that stores a product identifier, a field identifier, and a value. This can be difficult to query and gets tricky if you want to use specific data types, but it works.

-Ryan / Kardax

|||

Thanks. One thing that I should have mentioned is that my boss insists that we do everything in stored procedures - No dynamic queries.

After I made my post I contacted an old buddy of mine. If I understand your post ,I believe you both came up with the same answer. He suggested a Products table and a ProductsValue table. His idea that that the ProductsValue table would have one row for each field for the Product Type. For instance:

For the Product 'Paper' which has 10 fields the ProductValues table would have 10 rows. Each row would have the following fields: ProductCode, Field Name, FieldValue and FieldType.

Is this what you meant? If not I would appreciate an example to help clarify this.

Thanks

|||

Yes, that's exactly what I meant

It's neither fast nor efficient, but it'll let you stick with stored procedures.

-Ryan / Kardax

|||Thanks. I appreciate the advice.