Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Power of the Data Model

By David Poole,

When I was a DBA, I regarded a data model as either an as-is or to-be view of a database.  I saw it as something with three key uses.

  • Building database artefacts
  • Providing descriptions for the database artefacts
  • Showing how the database should be queried.

All true but a somewhat limited view of the power of a model.  Rather like stating that a rose is a coloured plant without appreciating its beauty or scent.

My eyes were opened during one of the sporadic periods when a business believes that hiring a consultancy will provide the answers it needs.  Back in those days I viewed consultants as an occasional distraction who charged handsomely for stating an opinion that the management team could have got by listening to their employees.  Generally, we gave the consultants the benefit of the doubt as most were benign and at least they would cause a decision to be made.  The worse decision being no decision.

The consultants who gave me a new perspective on modelling were somewhat different.  They were considerably older than the norm and the rumour was that they were all at or past retirement age and loved their work to the extent that rather than be bored to death in retirement had decided to form a consultancy to continue to do what they loved best. They asked a lot of questions, pored over our database diagrams and endeared themselves by making genuinely useful suggestions.  I am not sure what they made of our diagrams where we had attempted to represent several thousand tables for our many lines of business.  They were too polite to say.

After two weeks of question and answer sessions their modeller took me to one side and asked if I would take a look at a model he had produced.  What he showed me was 15 circles on a page representing the high-level entities our organisation used with interconnecting lines representing the relationships.  Nearly a decade on I keep looking at a dog eared copy of that model that I keep in the same place as my children’s birth certificates, mortgage details and other precious items.

So how can 15 circles on a sheet of A4 possibly represent an organisation with multiple databases and many thousands of tables supporting it?  What the modeller had done was produce a conceptual model of our organisation and had identified the core entities on which we should focus.  I picked a line of business and worked through the model. It was a good fit.  I picked one of our more obscure lines of business and again, it was a good fit.  I went on picking out lines of business and functions of our organisation and each time the model was a good fit.  At this stage I knew I was looking at something profoundly important but couldn’t put my finger on why such a high level artefact was important.

The answer came when the consultants started to show that model to our business colleagues.  What we noticed was that business people started to use the terms that were on the model.  People whose normal behaviour would conjour a mental image of herding cats or nailing jelly to the ceiling were all speaking the same language, thinking along parallel lines and that behaviour was being driven by the model!  The model was driving what Patrick Lenceoni calls “organisational clarity”.  It was encouraging communication and providing a framework with which the key stakeholders could speak a common language.

I have seen conceptual models since then, some of less complex domains, but none matched the simplicity of that 15 circle diagram.  It is sad that success in producing something simple is that the effort and intelligence required to achieve that simplicity is hidden by that simplicity.

That was simply the first of many lessons I learned that summer.  The consultants taught me that above all a model is a communication tool.  If your intended audience cannot understand it then it is a failed model no matter how technically correct it may be. They taught me that you should model entities for what they really are and not the purpose you are trying to put them to.  This is analogous to Dr. Ralph Kimball’s teaching that (in dimensional modelling) we should model the business process we wish to measure and not for the report we want to get out of the data.  To do otherwise risks developing a stove pipe solution that is inflexible and caters poorly, if at all, with the other uses to which the real-world entity can be put.

They stressed the importance of the data model being owned by someone outside of IT.  Their reasoning was that an actively engaged stakeholder is essential and the organisational expertise is the key to success of the data model rather than IT technical proficiency.  This also helps you to maintain focus on the data model as a means to drive communication rather than a sterile technical exercise.

Although they did not express it that way they were very much the proponents of “The genius of AND vs the tyranny of OR”.  The more technically detailed a model may be the harder it becomes for non-technical people to understand.  For that reason they regarded a technology assisted capability of being able to produce models from models as important.  A successful outcome is being able to produce a view of the model at the level that the audience is capable of understanding.

Various modelling tools do support the transformation of models as well as demonstrate the capability to generate actual code for what has been modelled.  This means that business conversations and IT action can be brought closer together.  Some people will find it counterintuitive but a robust modelling discipline can speed up development simply by promoting a shared understanding and lingua franca for communication.

Perhaps the strangest idea to absorb was that the databases actually plays a minor role in data modelling.  It is the final physical implementation of a model that has been used to demonstrate the validity of the proposed business process.  For this reason data modelling is just as important (if not more so) in the NOSQL world if we are to achieve true agility when handling data.

Total article views: 1608 | Views in the last 30 days: 1608
Related Articles

Modeling relational databases

Modeling relational databases


Database Modeling

which tools to verify a database model


Organisation Hierarchy level modelling

Please help database expert I'm using SQL 2000 server. My program can let user to define more th...


Modeling A Food Recipes Database

Modeling A Food Recipes Database

data modeling