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


Name value pair (EAV) model


Name value pair (EAV) model

Author
Message
Teshome-283915
Teshome-283915
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 716
Comments posted to this topic are about the item Name value pair (EAV) model
Anipaul
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11851 Visits: 1407
Part I of the article is good but I am more eager for its Part II. I will request him to write fast the Part II. Smile



Teshome-283915
Teshome-283915
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 716
I am nearly there and will try to get in as soon as I can.

regards

Teshome
SuperDBA-207096
SuperDBA-207096
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3737 Visits: 711
Looking forward to part 2.

I inherited a DB like this - using name value pairs. Biggest drawback: Performance!
Teshome-283915
Teshome-283915
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 716
Hi mark,

I am also looking forward to comments on part II of the article.

Thanks

Teshome
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)

Group: General Forum Members
Points: 338471 Visits: 42604
Part I is a good (possibly too brief) introduction to EAV's Wink Like the other's I'm looking forward to Part II.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Cade Roux
Cade Roux
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 491
EAV has its place. Regarding performance, indexes and efficient code can usually mitigate that.

I'd like to see more discussion about type-safety and whether there are any tricks for that which I haven't seen.
peterhe
peterhe
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4442 Visits: 453
SQL Server 2008 has better support for EAV by sparse columns/columns set



JRoughgarden
JRoughgarden
SSC-Addicted
SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)

Group: General Forum Members
Points: 445 Visits: 195
I am currently designing a DB that employs this name value pair approach to store arbitrary sets of attributes to be associated to a variety of entities. I am concerned about performance. but generally deal with with performance problems after they arise.

The product and marketing folks love the concept because it lets them promise anything to customers. They can avoid writing specs and get flexibility at the expense of performance, which is not their problem anyway. Rolleyes

One alternative seems to be entity tables with an ever-increasing list of nullable fields associated with the entities of interest, the union of all attributes. I'm not very keen on this approach either. Another approach is to have a master entity table with 1-1 subordinate sub-tables for the various entity types, but this also makes for a complicated schema.

I am setting the data type for the attribute values to be nvarchar, for localization. Three-character prefixes on the attribute names indicate the actual type of the value, e.g. strName, datEffective, smnPrice, etc. C# programmers are comfortable with the .Parse(string) functions and can retrieve the strongly typed actual value with these methods.

I hope this all works out, but any words of warning are much appreciated.

Jeff Roughgarden, MCDBA, MCSD



Cade Roux
Cade Roux
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 491
Since you are dealing with internationalization already, what form are you using for numeric data in the fields?:

9,999.99
9.999,99

Your C# code will need to parse with a specific locale, otherwise users changing their regional settings will result in differing formats.

This is a fundamental flaw in EAV models - interpretation of what is, effectively, free-form data that the database cannot help you enforce the rules on.

Even when it is only used for configuration, if the value is used as a threshold option like "Minimum Shipping Charge", it could come into calculations.

EAV is useful - it's great for options and configurations.

Not so good for the "designing a database within a database" idea.

But this problem of type-safety and validation has not yet been addressed to my satisfaction in any architecture I've seen.
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