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 :-
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 :
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 :
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. 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.