SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avoiding the Deep model (Key, value)


Avoiding the Deep model (Key, value)

Author
Message
mishka-723908
mishka-723908
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 245
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.
Michael Valentine Jones
Michael Valentine Jones
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23829 Visits: 11923
What is a deep-model?

Do you mean something like an entity-attribute-value (EAV) model?
mishka-723908
mishka-723908
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 245
Yes, like key-value pair.
Michael Valentine Jones
Michael Valentine Jones
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23829 Visits: 11923
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.
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34931 Visits: 7660
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
mishka-723908
mishka-723908
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 245
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101406 Visits: 18184
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 Modens 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)
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34931 Visits: 7660
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

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

Cool
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)
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34931 Visits: 7660
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
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