Database Design

[DeepBlerg]

Hi,

I need to make a database for a site which shows its product line. They have about 20 different product categories. The problem is, just about each product has different attributes, so for example, product(a) might have code, description, weight, price, pack price while product (b) might have code, description, color, dimensions, price. And so on and so forth. No two categories are the same. Apart from going back to the client and asking them to somehow simplify their product catalog is there a way around this without having to create seperate tables for each product in the database and defeating the purpose of dynamic content?

Thanks.

 

[Broccoli2]

Can you not simply add columns for all the possible attributes and allow them to be null?

Alternatively, if there really are a lot of different attributes, you could link the 'product' table to an 'attributes' table with a 'products_attributes' link table:

products(
ProductID int not null primary key auto_increment,
code varchar(20),
...
)
attributes(
AttributeID int not null primary key auto_increment,
Attribute varchar(30),
...
)
products_attributes(
LinkID int not null primary key auto_increment,
ProductID int not null,
AttributeID int not null,
AttributeValue varchar(100),
...
)

This would give you maximum flexibility and efficiency in your database, but might make your SELECTS a little more complex.

-Rob