0

I am building a model with the following requirements:

  • Items may have attributes
  • Attributes may be of type: 'Choice', 'FreeText', 'Number'

I am not quite sure how to relate the item to attribute properly. I currently have a table: 'ItemAttribute' which has both the item key and the attribute key and then just a value field which currently is simply a varchar field.

However, I would like to keep the integrity between the 'Choice' and the ItemAttribute which my current design does not help with.

How would you design this?

Thanks so much for your suggestions

Current Schema

Improve this question
  • Why don't you simply use a table with 3 columns, each corresponding to an attribute type? Or are there items connected with more than one value of a given attribute type? – dezso Nov 20 '12 at 11:05
  • An Item can have more than one attribute type. Or have none at all. – Chad Kapatch Nov 20 '12 at 15:45
  • Search for the term "Entity-Attribute-Value" (EAV). That is the name for this pattern. You'll find that it has many detractors and very few defenders. I myself have defended it under certain specific circumstances. You need to be more specific in your question about why you would take this design approach instead of modeling your items more directly. – Joel Brown Nov 21 '12 at 4:40
  • I have looked at the EAV model and am coming to the conclusion that it seems to be my only solution to this point. I would love to make this more "strict" however, the end user has a lot of customization (creating their own attributes to attach to an item) and as such cannot say with certainty that items have these many attributes with these specific choice options, etc... – Chad Kapatch Nov 21 '12 at 5:12