September 18, 2012 at 10:20 am
I've encountered a data design that is new to me. Frankly, I want to gouge my eyes out because it is a beat-down to work with. However, I am posting here to see if somebody can enlighten me about how this might be aN appropriate or useful data design. (I am receiving this from an external source and must incorporate it into our internal data.)
The following example is a fictional parallel used to illustrate this data design.
Table 1: Products
ProductIDProductName
123Widget
456Gridget
789Zidget
Table 2: ProductAttributes
ProductIDAttributeTableAttributeTableID
123Color12
123Size333
456Shape9
789Weight60
Table 3: Color
ColorIDColor
12Blue
13Red
14Yellow
15Green
Table 4: Size
SizeIDSize
123Small
124Medium
125Large
Table 5: Shape
ShapeIDShape
456Round
457Square
Table 6: Weight
WeightIDWeight
789Light
780Heavy
The booger is table 2. One would associate table 1 to table 2 based on ProductID. Then table 2 is used to look up attributes. The AttributeTable column tells you what table to look in and the AttributeTableID tells you what ID to use.
Also a product may have anywhere from zero attributes (rows in table 2) or as many rows as there are attribute tables.
Does this make any sense? Does this have any value? Is there a name for this approach? Is this common? Any thoughts are welcome.
Thanks.
September 18, 2012 at 10:28 am
What a coincidence. In a thread below that I read, I learned that this may be the EAV model.
September 18, 2012 at 10:30 am
Looks like a custom EAV using multiple tables instead of a single table.
September 18, 2012 at 12:40 pm
dglane (9/18/2012)
What a coincidence. In a thread below that I read, I learned that this may be the EAV model.
It does indeed look like a variant of EAV - a variant which uses individual attribute tables, instead of either one atribute table per attribute type or the most common variant which achieves a single attribute table by encoding all attributes as text strings (so all have varchar type - or maybe all have nvarchar type).
There are some systems for which an EAV model is a sensible design, but extremely few; but hype of EAV leads it to its being used where totally inappropriate, usually with appalling results. My advice is to avoid any such model unless you have checked very carefully indeed that it is appropriate for your applications and all likely future enhancements.
You might find this conversation or this one or even this topic useful if you are thinking of doing something with a model like this.
edit: fix URL tags
Tom
September 19, 2012 at 11:22 am
I've seen product tables like that. One advantage to them I see is that the organization using this can enter product attributes and values for those attributes without having to create new columns in the database.
September 19, 2012 at 1:58 pm
patrickmcginnis59 (9/19/2012)
I've seen product tables like that. One advantage to them I see is that the organization using this can enter product attributes and values for those attributes without having to create new columns in the database.
No, this variant doesn't allow that. It requires a new table to be introduced for each new attribute, and that new table has two columns - so it requires two new columns to be introduced.
Tom
September 19, 2012 at 2:04 pm
L' Eomot Inversé (9/19/2012)
patrickmcginnis59 (9/19/2012)
I've seen product tables like that. One advantage to them I see is that the organization using this can enter product attributes and values for those attributes without having to create new columns in the database.No, this variant doesn't allow that. It requires a new table to be introduced for each new attribute, and that new table has two columns - so it requires two new columns to be introduced.
Thats true. I was focused on the table with the attributes, my mistake!
September 20, 2012 at 5:35 pm
It could also be a way to facilitate multiple attribute values for the same product (red, blue).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply