Name value pair (EAV) model

  • Teshome-283915

    Old Hand

    Points: 367

    Comments posted to this topic are about the item Name value pair (EAV) model

  • Anipaul

    SSC-Insane

    Points: 24681

    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. 🙂

  • Teshome-283915

    Old Hand

    Points: 367

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

    regards

    Teshome

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Looking forward to part 2.

    I inherited a DB like this - using name value pairs. Biggest drawback: Performance!

  • Teshome-283915

    Old Hand

    Points: 367

    Hi mark,

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

    Thanks

    Teshome

  • Jeff Moden

    SSC Guru

    Points: 996502

    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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • Cade Roux

    Ten Centuries

    Points: 1326

    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

    SSChampion

    Points: 11363

    SQL Server 2008 has better support for EAV by sparse columns/columns set

  • JRoughgarden

    Ten Centuries

    Points: 1119

    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

    Ten Centuries

    Points: 1326

    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.

  • JRoughgarden

    Ten Centuries

    Points: 1119

    Yes, the C# code has to use the culture-aware parse method, and is aware of the requested culture.

    Int32..::.Parse Method (String, NumberStyles, IFormatProvider)

  • Cade Roux

    Ten Centuries

    Points: 1326

    I would definitely push this way down in a wrapper, so the app was a shielded as possible from the raw EAV. Less room for developers to make mistakes in their reading and writing.

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    There is a good deal of information on the web explaining why the EV model does not scale and does not perform well under load.

    No amount of indexing will fix the basic flaw in the EV design.

    Some people are fooled by the relative decent performance for single row and related row set retrieval but it is very easy to find fairly simple queries that do not run well with this design. Once you get 250 million rows of EV values like an application we purchased has you can pretty much schedule vacation while you wait for results.

    -- Mark D Powell --

  • JRoughgarden

    Ten Centuries

    Points: 1119

    Mark,

    Could you give some links to discussion on the (lack of) scalability of the EAV model?

    Also, what do you recommend in its place, master entity tables with 1-1 subtables for entity subclasses?

    Thanks,

    Jeff

  • Teshome-283915

    Old Hand

    Points: 367

    JRoughgarden (3/24/2008)


    Mark,

    Could you give some links to discussion on the (lack of) scalability of the EAV model?

    Also, what do you recommend in its place, master entity tables with 1-1 subtables for entity subclasses?

    Thanks,

    Jeff

    I will try to address the implementation specific of EAV in my part II article and hopefully it will answer all your concerns.

    regards

    Teshome

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply