Post reply

Entity-Attribute-Value designs

  • July 27, 2011 at 2:12 pm

    #254863

    I've heard and experienced many horror stories regarding EAV designs.

    Has anyone got any GOOD experience with these and if so please share?

    What sort of scale did you go to?

    How did you resolve the intrinsic problems with EAV designs?

    LinkedIn Profile

  • July 27, 2011 at 2:14 pm

    #1359657

    Nothing good.... and never heard a good success story on ssc.com either.

    Reading between the lines... stuck with a design and been told you can't change it?

  • July 27, 2011 at 2:35 pm

    #1359680

    Not yet but close.

    My experience, and I've had a lot of it, is similar to your own.

    I have seen it work in an e-commerce site where there was a large number of predefined fields and then an EAV over-spill for the inevitable differences.

    It worked reasonably well but I can't help thinking that it worked for a limited number of reasons.

    1. Most products fit very well in the predefined fields

    2. There weren't a great deal of records in the EAV structure

    3. All sorting and retrieval was done against pre-defined fields.

    In effect the EAV model was acting in a similar way to an xml blob or unpivoted sparse columns.

    I've seen Phil Factor's blog post that describes them as being like mumps. Everyone gets it at some point in their lives.

    To implement one properly you obviously have to build a database within a database.

    It strikes me that there should be a specific EAV engine rather than trying to put one inside an RDBMS.

    There has to be some use cases other than my e-commerce site that worked...........

    LinkedIn Profile

  • July 27, 2011 at 9:08 pm

    #1359787

    Not so much EAV's and certainly not OTLT's, but I do use their smaller cousin, NVP's (Name/Value Pair), a fair bit especially if reporting is required. They're also quite handy for certain Type 2 Slowly Changing Dimensions. On the flip side, they make audit tables a real PITA (IMHO).

    --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)

  • July 29, 2011 at 12:16 pm

    #1361008

    I'm happy with my EAV setup.

    My analysts create analytics and since there are a myriad way to analyze something its not hard to come up with new analytics. It happens about once a day. When they create something new I simply add the new analytic. I then can focus on the logic of the analytic rather than modifying table design.

    I have a nightly process which creates attribute values for each entity.

    The data output for this project is mostly single data points or lists so outputting to a report was very natural.

    Looks like this:

    MyEntity1

    Attribute1= 1

    Attribute2=1.5

    Attribute3=2

    or like this:

    MyEntity2

    Top Ten Attributes for Atribute10

    entitya-5

    entityb-6

    entityc-7

    Intrinsic problems? I haven't had to unpivot in data or pivot out data at all but that is probably specific to my situation. Performance is not an issue since its a once daily processing session and the server is not used on the overnight.

    I actually don't have an attribute table and I should probably fix that but it hasn't been a problem so far. I essentially combine the attributes and attribute values table. The table looks like this:

    SessionID-Each entity is associated with a sessionID, the related Sessions table contains the entity and a timestamp

    DataType-the attribute

    SubDataType-a further breakdown of the attribute

    DataPoint-the value

    PeriodStart-some of the values have valid start and end times

    PeriodEnd-some of the values have valid start and end times

    Sort-I'll add a custom sort if needed for presentation.

    I probably broke out of the classic EAV model with SubDataType. I use it like this as an example

    DataType-SubDataType-DataPoint

    HighTemp-January-80

    HighTemp-February-78

    HighTemp-March-81

    ...

    There's probably a better way to do this by creating a hierarchy within your attributes table. I would go in that direction if I had a 'third' level.

    Scale? I have about 40 entities. I have about 150 attributes.

    I think the model works for my situation. I didn't even know what EAV was until I was well into developing this model. If I had to do it all over again I would use the EAV model but would stick more closely to the classic EAV structure.

    I imagine a very small percentage of projects are suitable for an EAV model.

    My users are happy because I can often turn their requests for new attributes around in the same day.

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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