Results 1 to 3 of 3

Thread: Design Issue

  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Design Issue

    Hi

    I need a little direction with this design issue. Maybe someone here has run into the same issue before and can shed some light.

    I have 2 tables as follows :-


    Attribute
    with fields
    - id
    - attribute


    AttributeDescription
    with fields
    - id
    - description
    - attributeId



    1) An Attribute has many AttributeDescriptions that CAN be used to describe it and is related as follows :


    So lets say I have a Product now which can have attributes as follows :

    2) A Product can have 0 or 1 of each Attribute
    3) A Product must have 1 AttributeDescription for each attribute selected

    So I need to create a ProductAttributes table but this is where I run into a problem.

    Initially I thought my table should look like :

    ProductAttributes
    with fields
    - productId
    - attributeDescriptionId

    the two fields in the table would make up the primary key. But this leaves the table open to violation as we cannot enforce rule number 2.

    So my second thought was to design the table as follows :

    ProductAttributes
    with fields
    - productId
    - attributeId
    - attributeDescriptionId

    where productId and attributeId would make up the primary key and attribute is not null. This would then achieve rule 1,2 & 3 but now ProductAttributes is related to Attribute and AttributeDescription but AttributeDescription is also related to Attribute. This therefore creates a circular reference.

    Can this be overcome or is it acceptable in this scenario?

    Thanks

    Tecknick

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    It is acceptable. You can then use constraints to ensure that the chosen AttributeDescription belongs to the chosen Attribute:

    create table AttributeDescription
    ( id ... primary key
    , description ...
    , attributeId ...
    , UNIQUE (attribute_id, id)
    );

    create table ProductAttributes
    ( productId references Product(id)
    , attributeId references Attribute(id)
    , attributeDescriptionId ...
    , FOREIGN KEY (attributeID, attributeDescriptionId) REFERENCES AttributeDescription (attribute_id, id)
    );

    (BTW, I would say this model is veering ominously towards the egregious EAV model!)

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    Hey

    Thanks. Your suggestion will enforce the constraint and only allow for correct combinations. I read up on the EAV model and I can understand the point they are trying to make. I cannot see how I can overcome it though. Below is a textual description of the data and constraints. Your suggestions for a better setup of the tables would be appreciated, keeping in mind that I want to setup constraints to ensure the following criteria is met.

    - A set of Attributes exist (1000+)
    - A set of Attribute Descriptions exists (15000+)
    - An Attribute has many different descriptions available.
    - An Attribute Description belongs to only one Attribute

    - A set of Products exist (10000+)
    - A Product can have 0 or more unique Attributes
    - For each unique Attribute selected for a Product, the Product must also have one Attribute Description selected for that Attribute

    In other words there are a set of Attributes which a Product can have and also a set of Descriptions for each Attribute. When an Attribute is chosen for a Product, one Description for that Attribute must be chosen as well.

    Thank you in advance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •