Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

odd data design (at least to me) Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 10:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 24, 2012 3:25 AM
Points: 37, Visits: 129

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

ProductID ProductName
123 Widget
456 Gridget
789 Zidget

Table 2: ProductAttributes

ProductID AttributeTable AttributeTableID
123 Color 12
123 Size 333
456 Shape 9
789 Weight 60

Table 3: Color

ColorID Color
12 Blue
13 Red
14 Yellow
15 Green

Table 4: Size

SizeID Size
123 Small
124 Medium
125 Large

Table 5: Shape

ShapeID Shape
456 Round
457 Square

Table 6: Weight

WeightID Weight
789 Light
780 Heavy

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.
Post #1360886
Posted Tuesday, September 18, 2012 10:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 24, 2012 3:25 AM
Points: 37, Visits: 129
What a coincidence. In a thread below that I read, I learned that this may be the EAV model.
Post #1360889
Posted Tuesday, September 18, 2012 10:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 22,472, Visits: 30,138
Looks like a custom EAV using multiple tables instead of a single table.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1360892
Posted Tuesday, September 18, 2012 12:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1360948
Posted Wednesday, September 19, 2012 11:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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.
Post #1361528
Posted Wednesday, September 19, 2012 1:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1361608
Posted Wednesday, September 19, 2012 2:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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!
Post #1361610
Posted Thursday, September 20, 2012 5:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 01, 2013 1:18 PM
Points: 3, Visits: 9
It could also be a way to facilitate multiple attribute values for the same product (red, blue).
Post #1362384
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse