Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Antipathy for Entity Attribute Value data models


Antipathy for Entity Attribute Value data models

Author
Message
keith.fearnley
keith.fearnley
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 224
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.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4643 Visits: 9579
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.

create table Customer_Contact
(
primary key clustered ( Customer_ID, Contact_Type ),
Customer_ID int not null,
Contact_Type char(2) not null,
Contact_Code varchar(8000) not null
);

PH = Home Phone
PC = Cell Phone
PW = Work Phone
PA = After Hours Phone
PP = Primary Phone
PS = Secondary Phone
PE = Emergency Phone
EC = Personal Email
EW = Work Email
EP = Primary Email

34592 PH 555-381-0922
34592 PC 555-381-8111
34592 PW 555-576-0542
34592 PP 555-576-0542
34592 EC customer34592@gmail.com
34592 EP customer34592@gmail.com


"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."
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3120
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.

create table Customer_Contact
(
primary key clustered ( Customer_ID, Contact_Type ),
Customer_ID int not null,
Contact_Type char(2) not null,
Contact_Code varchar(8000) not null
);

PH = Home Phone
PC = Cell Phone
PW = Work Phone
PA = After Hours Phone
PP = Primary Phone
PS = Secondary Phone
PE = Emergency Phone
EC = Personal Email
EW = Work Email
EP = Primary Email

34592 PH 555-381-0922
34592 PC 555-381-8111
34592 PW 555-576-0542
34592 PP 555-576-0542
34592 EC customer34592@gmail.com
34592 EP customer34592@gmail.com


Personally I would have a PhoneContactPoint table and an EmailContactPoint table

CREATE TABLE PhoneContactPointType(
PhoneContactPointTypeID SMALLINT NOT NULL
CONSTRAINT PK_PhoneContactPointType PRIMARY KEY CUSTERED,
PhoneContactPointType VARCHAR(50) NOT NULL
CONSTRAINT UQ_PhoneContactPointType UNIQUE,
CONSTRAINT CK_PhoneContactPointType CHECK(LEN(PhoneContactPointType)>0)
)

CREATE TABLE PhoneContactPoint (
CustomerID INT NOT NULL
CONSTRAINT FK_PhoneContactPoint_Customer FOREIGN KEY REFERENCES Customer_Contact(CustomerID)
PhoneContactPointTypeID SMALLINT NOT NULL
CONSTRAINT FK_PhoneContactPoint_PhoneContactPointType FOREIGN KEY REFERENCES PhoneContactPointType(PhoneContactPointTypeID),
TelephoneNumber VARCHAR(25) NOT NULL,
CONSTRAINT PK_PhoneContactPointType PRIMARY KEY (TelephoneNumber ,PhoneContactPointTypeID )
)

Ditto EmailContactPoint.

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

LinkedIn Profile

Newbie on www.simple-talk.com
Peter Schott
Peter Schott
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 1906
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.



Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4643 Visits: 9579
David.Poole (1/21/2013)

...
...

Personally I would have a PhoneContactPoint table and an EmailContactPoint table

CREATE TABLE PhoneContactPointType(
PhoneContactPointTypeID SMALLINT NOT NULL
CONSTRAINT PK_PhoneContactPointType PRIMARY KEY CUSTERED,
PhoneContactPointType VARCHAR(50) NOT NULL
CONSTRAINT UQ_PhoneContactPointType UNIQUE,
CONSTRAINT CK_PhoneContactPointType CHECK(LEN(PhoneContactPointType)>0)
)

CREATE TABLE PhoneContactPoint (
CustomerID INT NOT NULL
CONSTRAINT FK_PhoneContactPoint_Customer FOREIGN KEY REFERENCES Customer_Contact(CustomerID)
PhoneContactPointTypeID SMALLINT NOT NULL
CONSTRAINT FK_PhoneContactPoint_PhoneContactPointType FOREIGN KEY REFERENCES PhoneContactPointType(PhoneContactPointTypeID),
TelephoneNumber VARCHAR(25) NOT NULL,
CONSTRAINT PK_PhoneContactPointType PRIMARY KEY (TelephoneNumber ,PhoneContactPointTypeID )
)

Ditto EmailContactPoint.

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."
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3120
@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.

LinkedIn Profile

Newbie on www.simple-talk.com
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4643 Visits: 9579
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."
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
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).
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4643 Visits: 9579
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."
BillyJack
BillyJack
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 65
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search