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 12345»»»

Name value pair (EAV) model Expand / Collapse
Author
Message
Posted Sunday, March 23, 2008 10:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:45 AM
Points: 25, Visits: 533
Comments posted to this topic are about the item Name value pair (EAV) model
Post #473346
Posted Monday, March 24, 2008 2:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:09 AM
Points: 5,338, Visits: 1,385
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. :)


Post #473379
Posted Monday, March 24, 2008 5:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:45 AM
Points: 25, Visits: 533

I am nearly there and will try to get in as soon as I can.

regards

Teshome
Post #473404
Posted Monday, March 24, 2008 5:27 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Looking forward to part 2.

I inherited a DB like this - using name value pairs. Biggest drawback: Performance!
Post #473407
Posted Monday, March 24, 2008 5:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:45 AM
Points: 25, Visits: 533

Hi mark,

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

Thanks

Teshome
Post #473413
Posted Monday, March 24, 2008 6:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
Part I is a good (possibly too brief) introduction to EAV's ;) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #473422
Posted Monday, March 24, 2008 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
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.
Post #473433
Posted Monday, March 24, 2008 9:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:04 PM
Points: 531, Visits: 431
SQL Server 2008 has better support for EAV by sparse columns/columns set



Post #473512
Posted Monday, March 24, 2008 10:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
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.

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



Post #473589
Posted Monday, March 24, 2008 10:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
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.
Post #473597
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse