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 123»»»

Avoiding the Deep model (Key, value) Expand / Collapse
Author
Message
Posted Wednesday, August 22, 2012 9:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
I am currently working with a group of folks to design a data model. Each record the main table in the model will have 7 attributes associated with it. there is a chance that in the future the 7 will become 8 and so on. There are some in the group that would like to create a deep-model becauase of that fact. I am very much against it as I think it would be much more difficult to code against. Does anyone have any ideas or opinions?

Thank you.
Post #1348537
Posted Wednesday, August 22, 2012 10:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,122, Visits: 11,408
What is a deep-model?

Do you mean something like an entity-attribute-value (EAV) model?


Post #1348550
Posted Wednesday, August 22, 2012 10:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
Yes, like key-value pair.
Post #1348551
Posted Wednesday, August 22, 2012 11:57 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 3,122, Visits: 11,408
I have never seen an implementation of EAV that was not a complete disaster.

Some of the main issues are:
Hard to maintain domain integrity.
Hard to maintain referential integrity.
Extremely hard to enforce not null requirements.
Queries take a long time to code and even longer to run.
Updates can easily cause widespread blocking.
All knowledge of the structure is contained only in the application, not in the data.

Post #1348618
Posted Wednesday, August 22, 2012 12:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 6,166, Visits: 7,230
There's a lot of material out there about Key/Value or EAV builds. They CAN be useful if you're sure your data will be homogeneous across all the values. They also under-optimize BADLY. For example... if you're looking for a series of associated attributes to an entity, do you want to pull back 7 rows (or 5 rows) instead of a single row? Answer: Most of the time, no.

In general, EAVs cause a lot more problems than they fix. Sure, the schema is more modular, but you've blown optimization to pieces, aggregations are a royal PITA (see IsNumeric() and WHERE clause for a simple example), and generally working with them is enough to cause permanent DB professionals to pull their hair out.

The schema can change later. Use column names (instead of *) in all your procs and use some common sense when expecting changes and you'll be fine, and the DB will love you for it.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1348619
Posted Wednesday, August 22, 2012 12:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
I understand and completely agree, but are there any other options? I dont see any but just add a column in the future when necessary.
Post #1348620
Posted Wednesday, August 22, 2012 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 13,126, Visits: 11,962
mishka-723908 (8/22/2012)
I understand and completely agree, but are there any other options? I dont see any but just add a column in the future when necessary.


Yes that would be better.

EAV is an "anti-pattern". They are nothing but a sure fire way to make sure you need to dust off your resume when it is "done". I don't want to pile on what the others have already said but consider datatypes. I didn't see this mentioned but you either have to use a sqlvariant (EEK!!!!) or use cast/convert everytime you have a number or a date (performance is trashed because nothing is sargable). I would avoid it at all costs.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1348654
Posted Wednesday, August 22, 2012 1:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 6,166, Visits: 7,230
mishka-723908 (8/22/2012)
I understand and completely agree, but are there any other options? I dont see any but just add a column in the future when necessary.


Dead on target. The only time you don't need a database developer is when the application is stagnant or you don't care about performance. If either of these are the case, you won't be adding columns (and indexing and proc cleanup and statistic checking and...), you'll be designing around your DB Dev or he'll be busy elsewhere until the project unstagnates.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1348665
Posted Wednesday, August 22, 2012 1:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 23,062, Visits: 31,589
I'm going to go against the flow a bit here. I think it really depends on what the application is intended to do. Where I work now I think the application I am helping to support would actually benefit from an EAV or hybrid EAV design.

Also, Microsoft themselves is using the EAV design in their Engineering Excellence Group to support compliance across product lines. Interviewed with the group 2 years ago bit didn't have the experience they needed.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1348666
Posted Wednesday, August 22, 2012 1:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 6,166, Visits: 7,230
Lynn Pettis (8/22/2012)
I'm going to go against the flow a bit here. I think it really depends on what the application is intended to do. Where I work now I think the application I am helping to support would actually benefit from an EAV or hybrid EAV design.

Also, Microsoft themselves is using the EAV design in their Engineering Excellence Group to support compliance across product lines. Interviewed with the group 2 years ago bit didn't have the experience they needed.


I would say that a well reasoned approach to EAV for particular datasets with an understanding of the underlying complexity and complications it can cause by a seasoned professional could and has been useful. Say, once in every few hundred databases.

I would also say that anyone looking to implement it for 'ease of development' and not 'optimization of attribute access' is approaching the process from the wrong side. I don't disagree with you Lynn, just that the wrong approach to allow for optimal usage is being taken here. It completely depends on the data and intended use, not the intended development environment.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1348668
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse