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

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!)

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.