• I've used them. Those and XML columns both work for "user-defined data structures". Which one I use depends on degree of flexibility needed and how it's going to be used.

    EAV is often easier, but XML has the advantage of the data all being in one place. If you delete a row, but not a set of rows, out of an EAV table, you've potentially corrupted the data. Not so with an XML column. XML also works nicely with .NET front ends, since the .NET languages have all kinds of XML shredding tools. Other application development scenarios are probably just as good at XML, but I don't know that one way or the other.

    On the other hand, EAV doesn't require jumping through any hoops to contain markup characters. And, if you include a column for each basic data type, and a column that indicates which type a particular row has in it, you can use that, plus a simple check constraint, to enforce pretty specific data integrity rules. Re-constituting the data is a performance killer every time I've seen these used, but it can be mostly overcome by some relatively simple tricks. Will never be fast, and doesn't index well, but it can be less-slow. It can also take a lot less disk space than XML, in some cases, and thus also less I/O work, less RAM, et al.

    I generally prefer XML columns for this kind of thing these days, but EAV has its place and its uses. It'd be more useful if T-SQL's Pivot and Unpivot operators weren't essentially junk.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon