EAV Yes or No : Reader Responses
There hasn’t been a lot of response to this topic, and, in database circles, this topic can be close to trolling for feedback. The responses today really added something to the discussion, and I’m glad to share them.
Mark:
My opinion, of course, is “It depends”. I have run into the issue you describe where code generation software does not understand what would be an optimal data model. If the application(s) using the data are all based on a specific framework the data model cannot fit into for some reason (Linq was the limitation I ran into), then the data model does not solve the issue for the business. If the development team responsible for the application does not understand proper use of the data model then it will most likely cause more problems than it solves.
I think the short answer is while the limitations of developers / development tools should not automatically exclude anything at the data layer, how the consumers expect to be able to use the data and what will be supportable with current resources does have to be a consideration in the decision.
As far as EAVs specifically I would say yes, as long as the partnership between development and data professions exists so the use will remain in the best interest of all.
Dareryll:
Regarding EAV data models, there are reference patterns available in the book, "The Data Model Resource Book, Volume 3"
I have made extensive use of EAV in a data warehouse that basically uses EAV to enable an entirely metadata driven ETL framework. However, the EAV data is pivoted (by stored procedure which are code-generated from the metadata in the EAV schema) into traditional Dimensional tables (which are code-generated from the metadata in the EAV schema).
This gives me a best of both worlds approach that provides for a metadata driven approach to data warehouse object management and ETL and provides structures which are optimal for BI applications.
I had to use this approach because in my environment there is constant attribute volatility in that the attributes dictionary is constantly being added to / subtracted from and I needed an architecture that supported this with minimal manual intervention / coding.
The bottom line: EAV architectures at present in a SQL Server environment only work as part of a multi-layered / multi-interface strategy. Until SQL Server supports high-performance, indeterminate column, on-the-fly, metadata driven (table schema?) pivoting and implements read/write ColumnStore indexes, EAV models are not in my opinion appropriate for delivery of data natively to reporting and BI applications.
Computer Professional Court
Today I had a few minutes to share war stories with colleagues about things that have gone wrong, what was the cause, and what could have been done to prevent it. As I’m preparing for this editorial my mind takes a weird tangent and asks the question, what if we were held accountable for these mistakes.
Here is an idea for a reality show…Computer Professional Court. Ok, I know it has a restricted audience. I think Dilbert Cartoons have demonstrated that many of the things we experience in our day to day lives working with Hardware and Software are common. I definitely started to see a lot of similarities in our horror stories earlier on.
So, to begin with we start with the rights of the professional. For a software developer, when he is read his rights it might sound something like “You will be held accountable for everything you write. Any code you submit to the source will be held against you in a court of your peers for accuracy, reliability, and performance”.
For a system administrator the rights might read, “You are accountable for every task you perform. Failing devices impacted by your changes will be held against you a court of your peers. Failure to maintain, or follow policy or procedures, or to expose faulty processes will be addressed.”
For an IT Manager it might read, “You are accountable for the smooth operations of the IT resources of your company. Inadequate resources of person or hardware, inaccurate procedures, or faulty auditing will be used against you in a court of your peers.”
Who knows, it could be fun just to think it through. Seems like a pretty good, short and sweet way to define a job description at a high level and set expectations. Who knows, some employer might even use it for annual compensation increases.
Well, that’s all for today. If you want to play the game, I’m sure you can come up with a much better reading of the rights, and share it with us by writing btaylor@sswug.org.
Cheers,
Ben
SSWUGtv
With Stephen Wynkoop
Did you miss Steve’s interview with Laura Rose about building your Management skills? Laura says, "Good manager create high-caliber teams that they enjoy and are proud of." But how can a manger be liked and trusted without sacrificing authority?
Find answers at the Show
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with SQL Server 2012 cursors
In this article, Alexander Chigrik explains some problems that you can have when you work with SQL Server 2012 cursors.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)