Post reply

Specific OLTP Design Question

  • October 20, 2008 at 10:56 am

    #94678

    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

    #887241

    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

    #887284

    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