Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Last word on dynamics tables/EAV structure?

    I'm working on a project that shares many of the same demands as this project described by this project for Yale university (frequent adding and removing of columns, objects that logically belong to the same structure but that might collect very different attributes) and as a result I began looking more heavily into EAV/CR models (where the attributes belonging to a certain table are collected in a separate attribute table which basically provides the attribute description while the values are collected in yet another table).

    Yet when I study conventional programming/database literature most naturally advises strongly against it.

    I was wondering if from a database POV it had already been decided that this should never be done? Or when it is done, what the thresholds usually are for when it becomes indeed the best choice at least for some tables (new columns added several times a day/month/year; flexibility for the user to create their own studies where on a very loose study structure they then decide which measurements are taken in their study; automatic form creation based on table description). Are there literature or tools which help doing EAV effectively? (one A and V table per table-table? What about number table, string table etc distinction?) What about other types of database (possibly non-relational)?

    It seems that there is quite a demand for EAV type flexible architecture in the scientific field. At least that is where I found most of the literature on the subject.

    Though I would naturally be very happy to receive any pointers I mostly wanted to know whether it is a dead end (if it should generally never be done under any circumstances) for my group to look into.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    last word: dont*



    *unless like virtually every rule in every sphere of life you have to
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by no means is EAV a "dead end"

    however, that article you linked to itself says right up front --
    Unfortunately, these tools must also re-implement certain features commonly taken for granted in a database engine, including typical methods of querying the logical schema of the data. Furthermore, the use of vertical storage can cause performance problems, particularly with attribute-centered queries.
    you should be okay as long as you are going in with your eyes open to the difficulties that you're going to encounter

    here are two articles which describe what's ahead for you --
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2009
    Posts
    2
    So, would a case where users are allowed to create their own research study templates be one of those cases where EAV might be appropriate?

    What alternatives are there? A new table for each study that is created? Wouldn't that make it rather hard to search/access/optimize the new table?

    One huge table which has columns for every possible thing that can be measured in a study and the user "templates" it in a way that they choose which columns they want to visible to them and all other values are set to NULL, table gets wider by one column every time somebody thinks of a new value to be measured? But where and how would be the best way to save which columns want to be "seen" by a particular user/study?






    (Yes I realize that in a way this is databases within database; )

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by LolaRuns
    So, would a case where users are allowed to create their own research study templates be one of those cases where EAV might be appropriate?

    What alternatives are there? A new table for each study that is created? Wouldn't that make it rather hard to search/access/optimize the new table?
    No, it would make it easier to search, access and optimize. Attributes that are common to more than one study can still go into one table. Attributes that are unique to one study or another should go in the tables for those studies. (I'm assuming only one table per study, which may or may not be the case I guess - tables need to be identified by common keys as well as common attributes).

    McGoveran and Date call this the Principle of Orthogonal Design and if you follow it then you will have minimal repetition of attributes in your schema. Orthogonal Design reduces redundancy, means you need less code to access any given attribute and because attributes are consolidated it is easier to create the right constraints and indexes to support them.

  6. #6
    Join Date
    May 2010
    Posts
    2

    Thumbs up Go for it

    EAV is fine, but don't roll-your-own.
    Use a mature ready-made solution which will mitigate the main valid argument against it... relatively slow performance. I'm not saying that all EAV systems become too slow, but unless certain steps are taken it probably will be.
    Creating your own requires quite a lot of upfront building work so use an EAV system that's already built and can deliver good caching for speed.
    Many large, successful, proprietary solutions use EAV, but rarely promote that fact. As a general rule of thumb, any system which allows an admin user to create, alter or remove tables (objects, content classes, content types, etc.) and their fields (attributes, properties, etc.) via a simple UI, at run-time is probably using some form of EAV model.
    There are mature development frameworks available that provide this model for general application development.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vlc View Post
    There are mature development frameworks available that provide this model for general application development.
    yeah, we see users of these systms occasionally here, asking for help with their SQL, hopelessly lost in how to assemble even a single a row of information...

    their table names usually start with jos_
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2010
    Posts
    2
    They probably a) don't understand this type of schema and b) are avoiding to use the API supplied with it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •