Relational databases and iterative development

Question

In many approaches to software development like agile methodologies, Domain-Driven Design and Object Oriented Analysis and Design, we are encouraged to take one iterative approach to development.

So we are not supposed to get our domain model done right in the first time we start working in the project. Instead, as time goes by we refactor the model because we gain deeper understanding of the problem domain with time.

Apart from that, even if we try to get a perfect model upfront, which I'm already convinced is very hard, requirements may change. So after the software has been deployed to production, the end users might notice that a certain requirement wasn't completely understood, or worse, some requirement was missing.

The point here is that we may end up needing to change the model after the software has been deployed. If this happens we have a problem: the production database has user's data which is important and is already fitted in the format for the old model.

Updating the code might be a hard task if the code is not well designed and if the system is big. But it can be done with time, we have tools like Git which help us do that without damaging the production-ready version.

On the other hand, if the model changes, if properties of classes disappear or whatever, the database should also change. But we have a problem: there's already data there which cannot be lost, which is already formated for the old model.

It seems that a relational database here is being a barrier preventing us from doing iterative development and even updating software when required by end users.

One approach I've already used was to code a special class which maps old database tables to new ones. So these classes pick data in old format, convert it to the format used by the new model, and save to the new tables.

This approach seems not to be the best one. My question here is: are there any well-known and recomended approaches to reconcile iterative development with relational databases?

Answer 1

It does not have to be special classes, but yes, you need something that will take the database in previous format and convert it to the current one.

The thing here is that you need to develop a process for writing and testing these scripts and discipline to never touch the testing and production databases by hand, but always by migration scripts.

Every time you need to do a change to the database, you write a script that will do it, whether in SQL or using your ORM layer, and commit it to your version control together with the changes that require the new schema. Then you have some control script that will upgrade the database by applying all the migration scripts that were not applied yet in a sequence.

And make sure you only ever modify any shared devel, test and QA environments by applying the scripts and rolling back to earlier version if they don't work, so you can be reasonably confident they will work as intended when you unleash them on the production.

New installation is simply done by applying all the scripts. After a time, you will might have hundreds of them and think that it is very inefficient, but don't fall into the trap of trying to optimize it. Installation is a one-time activity and keeping it reliable trumps making it fast.

@Doc Brown already linked Martin Fowler: Evolutionary Database Design and https://stackoverflow.com/questions/334059/agile-development-and-database-changes, and I'd add Alex Papadimoulis: Database Changes Done Right, which is shorter and has some examples.

As a decent example of tool implementing such process I suggest Alembic. It is based on the Python SQLAlchemy framework, but you can use it with other languages and frameworks if they don't have their own migration support. The Wikipedia page on Schema Migration lists more such tools.

Answer 2

Oddly enough, this is the very problem facing my current development team. The question contains several sub-questions, so they will be addressed independently.

First and foremost, does a relational database constrain the data model too much, making changes very difficult?

Most certainly, but not necessarily for the reasons cited. Unfortunately, the versatility of relational database management systems also lead to their downfall. The RDBMS was originally developed to offer a relatively simple data storage platform that would accept large data sets and reduce them to a relatively small size. This was done at the expense of complexity in the data model and computation power required. As database complexity increased, stored procedures, views, functions, and triggers came into being to help database administrators deal with the complexity in a consistent and scalable manner.

Unfortunately, the relational database model is not object-oriented, and does not naturally map to real-world entities as a data model should. That leads us to the need for middlemen tools like object-relational mappers and the like. Unfortunately, while these tools clearly have a place in today's development world, their use is merely targeted at a symptom of the relational data complexity problem, rather than the underlying cause, which is a misalignment of the data model to the real world.

That leads to the second part of the question, which was really more of an assumption, but should be viewed as a question: are we supposed to get our domain model done right the first time?

Yes, to an extent. As the question pointed out, it is rarely possible to fully understand the problem when we begin the design process. However, the difference between a completely incorrect data model, as opposed to one which may be tweaked as we gain greater understanding of the domain, is the model which coherently maps to the real world. This means that we must make every effort to create an initial data model that is consistent with our understanding of the problem in terms of its real-world entities. If we begin to normalize on the wrong entities, the data model will be wrong in two ways, and recovery will be difficult.

In many ways, the move to "No SQL" database solutions is a result of the problems of data model incoherence. Utilizing an object-oriented No SQL approach causes us to think more about the mapping between our objects in code and those in the real world- and when we run into an inconsistency, it often is self-evident because it is infeasible to implement in our database. This leads to better overall design.

That leads to the final question: is a relational data model inconsistent with the agile approach?

No, but more skill is required. Whereas in the No-SQL world, it is trivial to add a field, or to convert a property into an array, it is not at all trivial to do these things in the relational world. It takes, at a minimum, someone who is capable of understanding both the relational data model and the real-world entities they represent. This person is the individual who will facilitate updating the relational model as the understanding of the real-world model changes. There is no silver bullet to solve this problem.

Answer 3

The main point is not to refactor so much that your model changes beyond all recognition. Even with iterative development you should really be building on top of existing stuff and not refactoring it to pieces.

This gives you 2 main options to handle big changes when they come: the first is to build the DB layer as an API, use stored procedures so they can be changed to suit the client without changing the underlying data schema.

The other way is to replace tables with a bit of data migration. When a large scale change is required, you create the new schema and implement a set of scripts to take the old data and massage it into the new format. It costs time to do this which is why you rely more on cheaper methods of modifying the access to the data (eg via SPs) as a first choice.

So: 1. try to think ahead with design so you don't need to change things.

2. Rely on wrappers or APIs so change is limited or can be hidden inside an isolated component

3. Take the time to upgrade properly if you have to.

These steps apply to everything, not just databases.