August 22, 2012 at 9:56 am
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.
August 22, 2012 at 10:21 am
What is a deep-model?
Do you mean something like an entity-attribute-value (EAV) model?
August 22, 2012 at 10:22 am
Yes, like key-value pair.
August 22, 2012 at 11:57 am
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.
August 22, 2012 at 12:00 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 12:00 pm
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.
August 22, 2012 at 12:45 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2012 at 1:01 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 1:01 pm
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.
August 22, 2012 at 1:04 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 1:08 pm
Evil Kraig F (8/22/2012)
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.
And here is where we agree.
August 22, 2012 at 1:32 pm
The value data-type in this case will always be the same. I did not think using EAV, but several developers here thought that we should. Thank you everyone for your comments.
August 22, 2012 at 1:36 pm
Lynn Pettis (8/22/2012)
Evil Kraig F (8/22/2012)
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.
And here is where we agree.
Ditto. EAV does in fact have some value in VERY specialized situations. Kind of like cursors. They are good for a very small amount of things. From the OP statements and the fact they weren't really sure what that meant I took a pretty hard stance. I would say that unless you really know what you are getting into and can explain to a group of seasoned database folks why it is the best approach you are better walking away from EAV.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2012 at 2:07 pm
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.
I don't see why just adding a column when necessary is a problem.
It will certainly be less effort than the numerous disadvantages of the EVA model.
August 22, 2012 at 2:34 pm
This is one of my all time favorite threads about an EVA:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61024
I especially like the query posted by RobWafle at 02/01/2006 12:05:48 that had over 40 left joins.
I still stand by what I posted on that thread:
"..I think that the query you posted is a perfect illustration of the biggest disadvantage of the Entity/Attribute model, that it saves a little work up front in data modeling by allowing “open ended” insertion of new attributes at the cost of having to program the true data structure into each query. Of course, there are other annoying little problems, like enforcing not null, DRI, domain integrity, default values, check constraints, creating useful indexes, transactional integrity, etc. Basically, it takes all the most useful features of a relational data model, and throws them away..."
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply