* Posts by Ian Dundas

1 publicly visible post • joined 11 Aug 2007

Evolutionary vs. traditional database design

Ian Dundas

Modelling is about the organisation, not developing applications

As a senior DBA, who specialises in modelling and design, I have encountered many situations where "evolving" database designs based on developers' requests have resulted in extremely poor data integrity and consistancy. Unfortunately, I have also witnessed this becoming more prevalent in shops as the development team feels a need to produce results and show working prototypes as quickly as possible. It is not uncommon at all to have developers start coding long before analysis has been completed. The few projects that were allowed to have a significant amount of analysis done prior to the start of coding were amazing successful. When the developers finally got there hands on the databases, they were amazed as to how all their requirements were fulfulled in the design. The requests by developers for changes were very minimal, and those that were needed as a result of something missed during the analysis phase were easily incorporated. Properly constructed and QA'ed ERD models will reflect the rules under which the organization operates. ERD's and database schema's do not represent the processes that are followed. Database design lead by developers tend to be focused on what is displayed on a screen or report. This makes it very easy for them to retrieve and save data to the datastore, but is disasterous for the lifeblood of the corporation, the data. I have not come across many organizations that realise this before it is to late, the system has been in production for a number of years, and the integrity of the data has been compromised. One thing can be assured is that no matter how great the interfacing application is, if the data quality isn't there, the application will not be successful. While it may be a hard sell to have the Data Architects model the organization without an associated development project, it would be extremely helpful in the long run. Capturing the business rules and relationships independently of coding is one of the better ways of ensuring that models truly reflect the business.

That being said, during the design of the model, it should always QA'ed by numerous different people and not just those that were involved in its creation. It is not uncommon for the few business analysts working with the modeller to be unaware of aspects of their business that may be crucial. A quality model should allow me to sit down with a business client and tell him/her exactly the rules under which business is conducted. It always seems to amaze the clients when you walk into a room cold and explain to them their business just by walking through a quality model.

As for which methodology to follow, I prefer relational modelling and ERDs due to the stringent maths behind relational theory, conciseness of the models, and the ease to which they can be tested.

Obviously, everything can be for nought if the people involved in the analysis and creation of the models are not thorough or do not have sound understanding of relational modelling. Then the result is the worst outcome you can have, a bad model and a resulting database that the developer's cannot use. I have seen a few organizations where they deemed it necessary to employ Data Architects but then staffed the positions with people who were experienced with the business instead of someone versed in modelling principles and techniques. This can lead to some serious analysis issues as the person who "knows" the business will model what they know, not necessarily the business.

So, while Agile\Evolutionary development may be fine for applications, rarely will it result in a good model of the business and sound database design. It is too hard to find people with modelling skills to develop and maintain the models even when backed by solid analysis. To expect the all developers to have the skills and abilities to "evolve" good database design while they "evolve" their applications is extremely optimistic.