I agree with tymberwyld above. I normally limit EAVs to those things that cannot be known at design time.
I also think that the ability to datatype such a thing has been grossly underestimated. SQL_Variant does a dandy job there although I'll admit it provides no datatype enforcement by itself. While I also agree that contraints and FK's are all but useless, that's the whole point behind EAVs... they allow the end user to create "columns" on the fly. A trigger that "learns" could be applied to the table for a form of DRI and datatype enforcement. Yep... users can make a mess of such a table but such messes are sometimes best left to the customer.
Shifting gears, there is actually quite a bit of data that is better served in the form of an EAV. A simple example would be monthly plans vs actuals where you store the month or day or other temporal data in a column, the entity, the attribute (plan, actual, etc), and, of course, the value. Some would even call this particular type of EAV a "normalized fact table". Once you learn how to quickly aggregate and pivot the data, they're extremely useful and easy to manipulate. Of course, the cool part about this form of an EAV is that you can actually have DRI by entity and attribute and date/time.
Another very common use for EAVs is for auditing very wide tables. Yep... it can be a pain to figure out what a given row looked like at a given point of time the first time but, once you've done it, you won't think it so bad. Well, unless you're an ISO/ANSI purist. 😛
Also, in real life, I try to avoid storing XML anywhere in the database. It just seems as stupid as storing CSVs and certainly has more bloat both in parsing and storing. I'd much rather see the XML be shredded and propagated to real, normalized tables.
As far as polyglots go, I try to NOT go there. One app for the front end and one app for the backend is usually just right. Anything more is like a very complicated trip to work where you drive a car to a train station, walk from the train station to the curb where you pick up a bus that's not going directly to where you want it to go, get dropped off near work, cross several streets, take an elevator, follow a road map to your cube, and spend the next half hour reading emails that shouldn't have been sent to you to begin with before you can do any of your own work.
is pronounced "ree-bar
" and is a "Modenism
" for R
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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)