Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Antipathy for Entity Attribute Value data models Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 9:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:07 AM
Points: 242, Visits: 125
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.
Post #1409620
Posted Monday, January 21, 2013 10:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 1,660, Visits: 4,748
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
Post #1409634
Posted Monday, January 21, 2013 11:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:34 AM
Points: 2,904, Visits: 1,822
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
Post #1409661
Posted Monday, January 21, 2013 12:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:52 PM
Points: 841, Visits: 1,282
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.



Post #1409668
Posted Monday, January 21, 2013 12:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 1,660, Visits: 4,748
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.
Post #1409669
Posted Monday, January 21, 2013 12:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:34 AM
Points: 2,904, Visits: 1,822
@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
Post #1409673
Posted Monday, January 21, 2013 12:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 1,660, Visits: 4,748
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.
Post #1409674
Posted Monday, January 21, 2013 12:30 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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).
Post #1409677
Posted Monday, January 21, 2013 12:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 1,660, Visits: 4,748
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.
Post #1409678
Posted Monday, January 21, 2013 12:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:40 AM
Points: 37, 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.
Post #1409679
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse