Back to articles list
When writing a blog post on database modeling, you must be prepared that your abstract model doesn’t meet the needs of most readers. The reason is simple. Real-life database models are usually created in close relation to specific business and development requirements while the blog models are not.
For the last few weeks, I have been writing blog posts about creating database models. Topics ranged from a general approach to database modeling through a simple online forum to a model for a more complex online survey.
For each database model that I create, I try to clearly understand the business domain and work out in my mind the big picture of the model.
The Challenge of Abstract Database Development
Normally, as a solution architect, I take specific business requirements and convert those into the technical details of what needs to be created from the technical side: translating from the business language into the technical language – designing the algorithms at a high level and modeling the data requirements for the algorithms.
Unfortunately, I can’t blog about the real world database models that I create at work. For one thing, they are very specific to the business domain and for another I am restricted by confidentiality agreements. For the blog, I create a purely abstract concept with no specific business requirements except those that I imagine exist within the business domain. Now, that’s fine; I have a pretty good imagination and I point out frequently that your requirements may be different when I describe the choices that I am making. But this blogging process made me think about how different this process is from creating the models in a real project.
The Real-Life Development Process
In a real situation, I would work closely with the development team after creating the high-level solution and technical design in an interactive way so that the model fits the development needs.
The developers might complain that the data model is too normalized to support high-performance, or they might ask for additional normalization in certain areas. If any Alternate Keys were missing, the developers would complain pretty quickly and we would also notice it during performance testing of the database.
We would consider what the exact field lengths should be based on the maximum length of the data to be stored and on designs of screens for input and display of the data. Of course, the exact field lengths in a conceptual database model are not important.
We would work through examples of what data will be stored in the tables and how it will be used by the application, and create scripts to pre-populate the tables for unit testing of the application. In this way, we would catch the corner cases to ensure that the model supports the limits of the application.
So, basically, we would massage the model until it really supports the business and non-functional requirements of the system using an iterative process until we have evolved a model into something that we can all accept despite the compromises built into it.
As I said, it would be a very iterative process which might continue over many months while the application code, user interfaces and application interfaces are being developed.
Limitations of Well-Intentioned Feedback
In the current blogging situation, while my admittedly limited number of readers provides me feedback on issues and challenges that they observe with the models, it is necessarily superficial. I doubt that any of the readers are directly using the models to create an application and discover what really works and where there are issues.
So the comments like “model not well thought out” are almost certainly right. On the other hand, “there are FKs missing” are quite precise, but hopefully I have explained in the text of the article why I am including a foreign key or not.
Conclusion
Now, please don’t read this post as a complaint or a comment about the feedback that readers are making, rather I am reflecting on the difficulty of making a database model when you are not in an environment which allows an interactive exchange with an iterative development process.
There are probably other situations where database modelers are cut-off from the development process, but I have now realized how dangerous that would be and how prone to problems they would be.
Can you imagine a database model that is never changed? Never a single adjustment of a column, never the additional of a foreign key, never the need for a new table. Honestly, the only situation that I can imagine like that is when the application using the database is no longer evolving and has reached a dead end: end of life.
