October 20, 2008 at 10:56 am
Hi,
I've worked with many databases in the past 20 years, and [sadly] have never seen a great design for an OLTP system. So I'm posting to collect views/comments on the following.
I need to design a product database. Each product belongs to a category and each category has different attributes associated with it (eg. colour, size, style etc.). The number of values an attribute has varies enormously (eg. the number of colours is vastly greater than the number of styles).
Product to Category is 1to1
Category to Attribute is ManytoMany
I am struggling with the design structure. So far I have:
Product
(ProductID, ProductCategoryID etc.)
Category
CategoryID
CategoryName
CategoryAttribute (link table)
CategoryID
AttributeID
Attribute
AttributeID
AttributeName
Now I need to store the possible values for each attribute, which is not manytomany (each value only has meaning for a specific attribute):
AttributeValue
AttributeValueID
AttributeID
Value
All makes sense to me so far. The problem is how to structure the product table so that I know which attributes it has? I don't know how many attributes a product might have...could be 2 or 10.
So do I need a ProdutAttribute table, like this:
ProductAttribute
ProductAttributeID
ProductID
AttributeValueID
Thanks for your views!
Regards,
Richard
October 20, 2008 at 11:13 am
Your ProductAttribute is really a ProductAttributeValue table and it should not have the ProductAttributeID in it as it can be completely determined by the AttributeValueID.
Unless, the AttributeValueID is not really an Identity value (Primary Key) but only a subsequence number of ProductAttributeID.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 1:01 pm
Sorry I should have said the first ID field listed is a primary key of that table.
And you're right of course, the last table should be ProductAttributeValue and the two fields together are the primary key:
ProductAttributeValue
AttributeValueID
ProductID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply