How Deep is My Non-Love? Nested Dependencies and Overly Complex Design
Relational databases have this nifty concept of objects (just things, not code objects) being dependent upon other things. Sometimes those dependencies exist due to foreign key constraints, others via references to other things. One example of the latter can be found in VIEWs. A database VIEW is an object that references TABLEs or other VIEWS. Of course, if that VIEW references other VIEWs, then that view must reference TABLEs or another VIEW. And it’s that or another VIEW that can get modelers into trouble.
I reviewed a database design that had massively dependent VIEWs. How did I know that? I used a proper data modeling tool to look at all the dependencies for one central VIEW. And this is what my data modeling tool showed me:
That diagram shows how ONE VIEW is related to a whole bunch of other VIEWs and TABLEs in that design. In reviewing the model, I saw that many of the VIEWs appeared to be duplicates or had very high overlap of content with other VIEWs.
How do VIEWs Like This Happen?
There are many reasons one would created a nested VIEW. Like anything in a hierarchy, you could have objects that could be used independently and as part of a group on a regular basis. But that only explains one level of a VIEW hierarchy (nest). What about VIEWs that are nested dozens are levels deep? And why would a database have such a complex design around one VIEW? These are the most common reasons I run into bad practices with VIEWs:
- Designers who don’t understand the massive performance loss for massively nested VIEWS
- Designers who design for theory, not for real world data stories
- Designers who have no idea they are referencing another VIEW when they design their VIEW
- Designers who are following a worst practice of creating a VIEW for every report and every window in an application
- Designers who don’t collaborate with other designers and create their own set of VIEWs and dependencies
- Designers who are compensated for doing work fast and not well
- Designers who use DDL to do design, therefore never seeing the complexity of their designs
- Data Governance policies that let anyone create objects in a database
- A team environment were “everyone is a generalist”.
I could go on. While I can’t go into details here, in my review I recommended complete refactoring of this overly complex design. It is my guess this complexity was contributing to performance problems experienced in this application. I also recommended that professional designer was used to refactor other issues with the database design. I have no idea if this happened. But I doubted that this application was going to meet its large scale web application goals.
Why Am I Sharing This?
Because so many design issues I find in reviews have the same causes for performance and data quality issues I’ve listed above. I find that not using a real data modeling or design tool is the main contributing factor. There’s a reason why physical world architects and engineers use drawings and architectural diagrams. Models are also how they make modifications successful to the items they build.
Yes, physical objects are different than software/application/database objects. My position is that these latter objects need models at least as much as buildings and devices do. We need tools to reverse engineer objects, to view the dependencies, to search, and to assess. In other words, to model. Engineering data solutions requires engineering tools like data modeling tools. And, yes, data engineers to understand how to use those tools and how to model out the unnecessary complexity.
Leave a comment
Subscribe via E-mail
Recent Comments
Categories
Downloads
- 476275616.jpg
- favicon
- Refactoring Computer Engineer Barbie
- 10 Things I Hate About Interviewing with You
- EDW 2013 Karen Lopez Get Blogging
- Karen Lopez presentation DAMA PS 2012
- Data Modeling Contentious Issues - DAMA Nebraska
- Karen Lopez - 10 Physical Blunders - DAMA
- Career Success In Data Profession - DAMA
- The Straw Poll
- You've Just Inherited a Data Model CheckList
- KarenLopez - 5 Physical Blunders - 24HOP-2011
- Handouts for OEMUG / CA Global Modeling User Group Why Be Normal Webcast
- Handouts Database Design Contentious Issues - New York 2010
- Handouts Database Design Contentious Issues - DC 2010
Archive
Recent Posts
Archives
- November 2018
- May 2018
- April 2018
- December 2017
- August 2017
- September 2016
- August 2016
- June 2016
- May 2016
- April 2016
- March 2016
- February 2016
- January 2016
- December 2015
- November 2015
- September 2015
- July 2015
- June 2015
- May 2015
- April 2015
- March 2015
- February 2015
- January 2015
- December 2014
- November 2014
- October 2014
- August 2014
- July 2014
- June 2014
- May 2014
- April 2014
- March 2014
- February 2014
- January 2014
- December 2013
- November 2013
- October 2013
- September 2013
- August 2013
- July 2013
- June 2013
- May 2013
- April 2013
- March 2013
- February 2013
- January 2013
- December 2012
- November 2012
- October 2012
- September 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- September 2010
- August 2010
- July 2010
- February 2009