SQL Clone
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211425 Visits: 41977
lnardozi 61862 (1/28/2013)
I wouldn't want to change the table structure every time a requirement changes.
The DBA has better things to do, and many DBAs are not noted for their SQL programming skills.
Above all, I want my design to be flexible and maintainable by someone else but me.


Now I'm really curious. What does changing a table structure have to do with SQL programming skills?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211425 Visits: 41977
long_shanks64 (1/23/2013)
BillyJack

Q. Who implements the restrictions as to which business functions and 3rd parties have access and update permissions to this new attribute. Who decides how this new attribute will be initialised?
answer: A IT. B. it doesn't matter C. we don't have time

Q. Presumably this new attribute is of a certain data type, with constraints as to permissible values (possibly restricted to other existing entities and attributes). Who enforces this rule?
answer: A IT. B. it doesn't matter C. we don't have time

Q. Who make changes to the data capture applications? Or to the ETL to extract data to 3rd party systems. Or the existing operational reports. Or the existing analytical reports?
answer: A IT (and their BI counterparts) B. it doesn't matter C. we don't have time

Q. Who updates the enterprise data model and users' metadata dictionary?
answer: A IT (and their BI counterparts) B. it doesn't matter C. we don't have time

Q. Who enforces data quality checks on the new attribute?
answer: A IT. B. it doesn't matter C. we don't have time

If you answered A to these questions - IT are responsible for implementing these rules and controls. So they need to accommodate the new requirement AS PART OF THE OVERALL SOLUTION in a modification to the existing data model.

If you answered B or C to these questions - w00t please see me after class.


Just a little chippy for someone that doesn't have the correct answer listed for any of the questions. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
BillyJack
BillyJack
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 65
Jeff Moden (1/28/2013)

Heh... wait a minute now. Are you saying that you work in an environment where developers can make physical table changes directly in the production environment without testing/etc and without going through a DBA? Or is that what you're trying to avoid with the use of EAVs?


The point I am trying to make is that with an EAV model, you don't need to do any table maintenance in order to add new attributes. They are simply new rows of data added to the EAV tables
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25193 Visits: 12464
long_shanks64 (1/23/2013)
The 'alternative' is to model all known attributes correctly in the first place using declarative referential integrity and constraints.

Sometimes it's just not possible.
Classic example is recording addresses.
Here in NZ we have frequently deal with suppliers/distributors which do not separate Australian and NZ markets.
Therefore, if I want to register on such site I need enter my address into the fields matching AUS address definitions.
In Australia it's mandatory to fill "State" field, and in NZ we do not have neither states, nor provinces, only separation by North Island and South Island, which Australians do not understand. So, I have to play "guessing game" every time filling such form - what should I put into State field marked with a red star (mandatory) to match their expectations.

In proper design the field should be not-nullable with limited set of allowed values for AUS addresses and NULL for all NZ addresses.
Obviously, you cannot implement it with a "normal" design, as the number of fields is dynamic, and constraints are different from case to case.

You can choose, of course, to go with separate tables for each coutry, but then you need to run search requests against multiple tables.
And when you pull an address for a customer you need to use either dynamic SQL or include 2 or more tables into your SQL requests.

To me it looks much more ugly than any "in code" check constraint implemented in "saving address" procedures.
lnardozi 61862
lnardozi 61862
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 617

Now I'm really curious. What does changing a table structure have to do with SQL programming skills?

--Jeff Moden


I'm sure you've seen this hundreds of times before - you're one of the brighter lights on this board. You need some configuration data stored somewhere, so a table is created. Then another and another and before you know it you've got a few hundred. Those few hundred tables each have their own queries, their own table design, they end up getting used in joins - sometimes very badly. When the tables get created, do they have the correct indices? What is involved enterprise wide when it's time to change one of those tables - particularly one used in several hundred queries? Assuming your DBA has the time to look at all those queries (mine doesn't), what are the odds he'll hit every single one right on the head? My system is heavily optimized for retrieval and supports everything except binary - which means the TVFs can be used in stored procs, views, web services, wcf services, restful services, Json services and Sharepoint. Anything in our enterprise that might have had hard coded information in instead table driven with no duplication of data and a well defined map that explains the dependencies enterprise wide. Sniff if you want, but it's the best thing I've ever done in almost 30 years of programming.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211425 Visits: 41977
BillyJack (1/29/2013)
Jeff Moden (1/28/2013)

Heh... wait a minute now. Are you saying that you work in an environment where developers can make physical table changes directly in the production environment without testing/etc and without going through a DBA? Or is that what you're trying to avoid with the use of EAVs?


The point I am trying to make is that with an EAV model, you don't need to do any table maintenance in order to add new attributes. They are simply new rows of data added to the EAV tables


Yep. I agree. I've been there. It does make such a thing wicked easy. And it's simple enough to pivot the data into a table-like result set when needed by using a bit of dynamic SQL.

It is a bit wicked, though. How are you handling similar but different attribute names for the same thing? For example, in address related data, you might have an attribute for "Street Number". Not including the potential for phat phingering, I can think of at least 15 different ways that might be entered as an attribute name. Short of a huge list of possible attribute names and a "simto" search, I've not found a way to overcome that problem.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211425 Visits: 41977
lnardozi 61862 (1/29/2013)

Now I'm really curious. What does changing a table structure have to do with SQL programming skills?

--Jeff Moden


I'm sure you've seen this hundreds of times before - you're one of the brighter lights on this board. You need some configuration data stored somewhere, so a table is created. Then another and another and before you know it you've got a few hundred. Those few hundred tables each have their own queries, their own table design, they end up getting used in joins - sometimes very badly. When the tables get created, do they have the correct indices? What is involved enterprise wide when it's time to change one of those tables - particularly one used in several hundred queries? Assuming your DBA has the time to look at all those queries (mine doesn't), what are the odds he'll hit every single one right on the head? My system is heavily optimized for retrieval and supports everything except binary - which means the TVFs can be used in stored procs, views, web services, wcf services, restful services, Json services and Sharepoint. Anything in our enterprise that might have had hard coded information in instead table driven with no duplication of data and a well defined map that explains the dependencies enterprise wide. Sniff if you want, but it's the best thing I've ever done in almost 30 years of programming.


I try not to sniff at anything except unnecessary RBAR or the improper use of CLR. :-D

Unless I'm mistaken, you talking about a bunch of similar small reference tables that may have an ID, a code, and a description in them? If that's correct, I don't see those tables ever having a new column being added to them. Could you explain why you might want to add a column to such a reference table?

If you have hundreds of them, I can see where adding new data to them could be a bit of a problem because you'd have to find the right one first. That would be easy if DRI were properly instantiated at table creation time.

Of course, I might be misunderstanding what you mean. Are you, in fact, saying that you consolidated all such reference tables into a single EAV? If so, how are you handling the implicit conversion problems that destroy SARGability during joined lookups? Did you use SQL_Variant to fix that particular problem or some other method?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
BillyJack
BillyJack
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 65
Jeff Moden (1/30/2013)

It is a bit wicked, though. How are you handling similar but different attribute names for the same thing? For example, in address related data, you might have an attribute for "Street Number". Not including the potential for phat phingering, I can think of at least 15 different ways that might be entered as an attribute name. Short of a huge list of possible attribute names and a "simto" search, I've not found a way to overcome that problem.


One of the requirements I've always had when an EAV model is to be implemented is that the business owner MUST have someone (a data czar for lack of a better name) responsible for managing new attribute setup (and their valid values) in the system. If not, you will inevitably wind up with multiple attribute names representing the same thing as you indicated, and you will be writing a "merge" proc to consolidate all of the attributes and their values under the proper attribute name. So, with built in business flexibility comes added business responsibility...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211425 Visits: 41977
BillyJack (1/31/2013)
Jeff Moden (1/30/2013)

It is a bit wicked, though. How are you handling similar but different attribute names for the same thing? For example, in address related data, you might have an attribute for "Street Number". Not including the potential for phat phingering, I can think of at least 15 different ways that might be entered as an attribute name. Short of a huge list of possible attribute names and a "simto" search, I've not found a way to overcome that problem.


One of the requirements I've always had when an EAV model is to be implemented is that the business owner MUST have someone (a data czar for lack of a better name) responsible for managing new attribute setup (and their valid values) in the system. If not, you will inevitably wind up with multiple attribute names representing the same thing as you indicated, and you will be writing a "merge" proc to consolidate all of the attributes and their values under the proper attribute name. So, with built in business flexibility comes added business responsibility...


Concur and that's where I was going to on this one. In every case except for one internal app, we made it so that only those folks with such responsibility could actually add attributes. Is it idiot proof? Not by a long shot but the customers sure were/are happy about it.

We did go a bit further, though. We made it so that the unique attributes could be listed for any given entity (there weren't THAT many because the rest of the system was properly normalized) and then through a simple system of check boxes, made it so that one of the data czar's you spoke of could combine attributes for that given entity. As you can well imagine, the stored procedure for such an update was incredibly simple and didn't even require any dynamic SQL.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28600 Visits: 11495
What about containing customer contacts?
I still that one Customer_Contact table that contains something like Customer_ID, Contact_Type, Contact_Code columns (Entity-Attribute-Value ???) is better than seperate Customer_Phone, Customer_Fax, Customer_Email, Customer_Tweet, ad nauseam tables.


"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."
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