<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Database Design
»
Design Ideas and Questions
»
odd data design (at least to me)
odd data design (at least to me)
Rate Topic
Display Mode
Topic Options
Author
Message
dglane
dglane
Posted Tuesday, September 18, 2012 10:20 AM
SSC 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
dglane
dglane
Posted Tuesday, September 18, 2012 10:28 AM
SSC 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
Lynn Pettis
Lynn Pettis
Posted Tuesday, September 18, 2012 10:30 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 21,602,
Visits: 27,425
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
L' Eomot Inversé
L' Eomot Inversé
Posted Tuesday, September 18, 2012 12:40 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 7,087,
Visits: 7,142
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1360948
patrickmcginnis59
patrickmcginnis59
Posted Wednesday, September 19, 2012 11:22 AM
SSC-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
L' Eomot Inversé
L' Eomot Inversé
Posted Wednesday, September 19, 2012 1:58 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 7,087,
Visits: 7,142
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1361608
patrickmcginnis59
patrickmcginnis59
Posted Wednesday, September 19, 2012 2:04 PM
SSC-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
mwolfstone
mwolfstone
Posted Thursday, September 20, 2012 5:35 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, December 05, 2012 8:58 AM
Points: 3,
Visits: 8
It could also be a way to facilitate multiple attribute values for the same product (red, blue).
Post #1362384
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
