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 «««34567»»»

Antipathy for Entity Attribute Value data models Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 8:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412733
Posted Monday, January 28, 2013 8:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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 - 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412739
Posted Tuesday, January 29, 2013 7:44 AM
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
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
Post #1413054
Posted Tuesday, January 29, 2013 10:17 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 4,576, Visits: 8,342
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.
Post #1413442
Posted Tuesday, January 29, 2013 10:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:16 PM
Points: 107, Visits: 511

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.
Post #1413446
Posted Wednesday, January 30, 2013 6:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1413612
Posted Wednesday, January 30, 2013 7:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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.

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1413623
Posted Thursday, January 31, 2013 10:11 AM
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
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...
Post #1414248
Posted Thursday, January 31, 2013 12:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1414279
Posted Thursday, January 31, 2013 12:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 1,604, Visits: 4,594
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.
Post #1414286
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse