SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


odd data design (at least to me)


odd data design (at least to me)

Author
Message
dglane
dglane
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 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.
dglane
dglane
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 129
What a coincidence. In a thread below that I read, I learned that this may be the EAV model.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: General Forum Members
Points: 150752 Visits: 39285
Looks like a custom EAV using multiple tables instead of a single table.

Cool
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)
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36149 Visits: 12792
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

patrickmcginnis59
patrickmcginnis59
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2415 Visits: 2333
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.
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36149 Visits: 12792
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

patrickmcginnis59
patrickmcginnis59
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2415 Visits: 2333
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!
mwolfstone
mwolfstone
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
It could also be a way to facilitate multiple attribute values for the same product (red, blue).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search