As I understand it, that's exactly what Anchor Modeling (see earlier post) is trying to achieve, since new attributes can be added without detriment to the existing schema and with auto-generated code to ease the use of the added complexity required to allow this. That's one reason it interests me, though I do have my doubts.

Customer contact information is an example of where an entity-attribute-value model makes sense. The design is simple, efficient, and can be easily extended to support any type of contact, wether it be G.O.T. style messenger ravens or yet to be defined holograms.

Eric M Russell (1/21/2013)Customer contact information is an example of where an entity-attribute-value model makes sense. The design is simple, efficient, and can be easily extended to support any type of contact, wether it be G.O.T. style messenger ravens or yet to be defined holograms.

For Customer Contacts, why wouldn't I make a "Contact Type" table and just normalize this? Sure it can store just about anything, but since all contacts are going to be a string of some sort, I don't see the value in not going the one extra step and normalizing. With the existing model, I still have to either know what the two-character code represents or have to maintain a lookup table somewhere. I could also potentially run out of combinations or be forced to make new entries that don't make sense just so they're unique ("XX" - what does this mean again?). (And yes, this is highly unlikely, but it could potentially happen.)

Regarding the article, I appreciated the examples in the article and the practical cases for them. Too many times we get someone modeling out an EAV config just because it seems expedient when it will more than likely cause issues later down the line. I've also heard "we'll prevent bad data through the code" too many times. That's never worked well in my experience, though hoping others have had better experience.

You could even generalise EmailContactPoint to InternetContactPoint if you wanted blog sites and Twitter details

Yeah, but to what end? There is no practical reason to partition internet based contacts from telecom contacts in the data model. Most phone or fax calls today are done using an internet enable device, wether it be iPhone, Skype, or magicJack. I still have the same 999-999-9999 I had ten years ago, only it's since been ported over from an analog provider to magicJack.

"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."

@Eric, it depends on what you want to do with those contact details. If it is just a block of unstructured data that you want to eyeball whenever you reference the customers then fine.

If you want to integrate it with separate telephone dialling systems and email targetting systems then I'd normalise it out.Ditto security. Separation of data to support different contact methods allows me to assign different security based on the sensitivity of the data. Email addresses tend to be less sensitive than telephone numbers.

Frankly it is so easy to normalise your example that it simply wouldn't occur to me to put it in an EAV. You have disciplined data and you are going to store a lot of it if it is a contact system. I regard EAV as an option of last resort, not a starting point to be optimised later.

The medical example early in this thread is an example where EAV genuinely is useful. Once you get past 40 the sheer number of things that can go wrong with a human body becomes horribly apparent and I sooner try and normalise the human body than represent its ailments in an EAV form.

Peter Schott (1/21/2013)For Customer Contacts, why wouldn't I make a "Contact Type" table and just normalize this? Sure it can store just about anything, but since all contacts are going to be a string of some sort, I don't see the value in not going the one extra step and normalizing. With the existing model, I still have to either know what the two-character code represents or have to maintain a lookup table somewhere. I could also potentially run out of combinations or be forced to make new entries that don't make sense just so they're unique ("XX" - what does this mean again?). (And yes, this is highly unlikely, but it could potentially happen.)

Regarding the article, I appreciated the examples in the article and the practical cases for them. Too many times we get someone modeling out an EAV config just because it seems expedient when it will more than likely cause issues later down the line. I've also heard "we'll prevent bad data through the code" too many times. That's never worked well in my experience, though hoping others have had better experience.

I actually do have a Contact_Type table and a foreign key constraint. Contact_Type can have additonal columns to indicate things like if it's a supertype of phone number, email, etc. but there has never been a requirement to take it that far.

"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."

Using SQL_variant data type for Value sends half of the article's concerns to rubbish.Including data size calculations.Filtering by comparing to a datetime value will show only records having datetime base type, no varchar values like 2012-01-21.

And if you do not know how to retrieve items with all 3 attributes having requested values it indicates weakness of your SQL skills, not EAV model.

To enforce data types for certain attributes you may simply use views, triggered if you use it for uploading data.You simply define a data type in columns definitions and only appropriate values may be inserted into those columns. No additional coding required.

About locking.When you update an attrribute in a traditional model you lock at least the row having that item.Whole row. Including all attributes.If another process is trying to update another attribute it has to wait until the previous update is completed and the lock is released.And after that it will overwrite the whole row, including the just updated attribute, setting it back to previous value.Unless you take special precautions for this case in your code - talking about additional coding!!!

And XML.Oh, right XML!Best alternative!Hierarchical list of attributes with associated values.Nothing like EAV!And even better - attributes are named in a human language every time they are mentioned. Definitely less storage space than using integer ID's.And values are all strings, no way around.Especially good when you share the data with representative offices in Germany, Russia and Korea. Dates saved in local formats (even if you do not allow words for months, still mdy is "only in America" format).To search against XML you need additional "XML index" - talking about duplicating data storage and extra CPU/IO load (to update the index you actually need to parse uploaded XML and rewrite the index pages).

David.Poole (1/21/2013)@Eric, it depends on what you want to do with those contact details. If it is just a block of unstructured data that you want to eyeball whenever you reference the customers then fine.

If you want to integrate it with separate telephone dialling systems and email targetting systems then I'd normalise it out.Ditto security. Separation of data to support different contact methods allows me to assign different security based on the sensitivity of the data. Email addresses tend to be less sensitive than telephone numbers.

Frankly it is so easy to normalise your example that it simply wouldn't occur to me to put it in an EAV. You have disciplined data and you are going to store a lot of it if it is a contact system. I regard EAV as an option of last resort, not a starting point to be optimised later.

The medical example early in this thread is an example where EAV genuinely is useful. Once you get past 40 the sheer number of things that can go wrong with a human body becomes horribly apparent and I sooner try and normalise the human body than represent its ailments in an EAV form.

I still don't see the practical benefit of seperating phone / fax numbers from emails and twitter accounts. It seems like an arbitrary distinction that can change over time or depending on the context. The Contact_Type tells the application wether to enable an autodialer versus a hyperlink on the font end.

"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."

The Anchor Modeling approach is interesting, but I imagine that an environment where there are many different entities each with hundreds or maybe even thousands of attributes are added over time (where an EAV really shines) would become difficult to manage since it looks like each attribute requires it's own table.