Avoiding the Deep model (Key, value)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Design Ideas and Questions » Avoiding the Deep model (Key, value)

21 posts, Page 1 of 3

1 2 3 »»»

Avoiding the Deep model (Key, value)

Rate Topic

Display Mode

Topic Options

Author

Message

mishka-723908

Posted Wednesday, August 22, 2012 9:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 2:11 PM
Points: 45, Visits: 175

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

Michael Valentine Jones

Posted Wednesday, August 22, 2012 10:21 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:35 PM
Points: 2,969, Visits: 10,620

What is a deep-model?

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

Post #1348550

mishka-723908

Posted Wednesday, August 22, 2012 10:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 2:11 PM
Points: 45, Visits: 175

Yes, like key-value pair.

Post #1348551

Michael Valentine Jones

Posted Wednesday, August 22, 2012 11:57 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:35 PM
Points: 2,969, Visits: 10,620

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

Evil Kraig F

Posted Wednesday, August 22, 2012 12:00 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:35 PM
Points: 5,722, Visits: 6,194

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

mishka-723908

Posted Wednesday, August 22, 2012 12:00 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 2:11 PM
Points: 45, Visits: 175

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

Sean Lange

Posted Wednesday, August 22, 2012 12:45 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 8,980, Visits: 8,540

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

Post #1348654

Evil Kraig F

Posted Wednesday, August 22, 2012 1:01 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:35 PM
Points: 5,722, Visits: 6,194

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.

Post #1348665

Lynn Pettis

Posted Wednesday, August 22, 2012 1:01 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 21,832, Visits: 27,862

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)

Post #1348666

Evil Kraig F

Posted Wednesday, August 22, 2012 1:04 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:35 PM
Points: 5,722, Visits: 6,194

Lynn Pettis (8/22/2012)

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.

Post #1348668

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions