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?

  • 6
    Incidentally, I don't think this has anything to do with relational databases in particular. I have a similar problem with a project I'm working on, but we're having it with the schema for our JSON strings that represent very non-relational objects. It probably affects all forms of persistence equally.
    – Ixrec
    Commented Feb 21, 2016 at 21:13
  • 1
    You change the database schema in a way that doesn't lose data, en.wikipedia.org/wiki/Schema_migration . Commented Feb 21, 2016 at 21:18
  • 1
    I am sure this topic was extensively discussed somewhere before, just can't find it on Programmers. But see here martinfowler.com/articles/evodb.html or here stackoverflow.com/questions/334059/…
    – Doc Brown
    Commented Feb 21, 2016 at 22:30
  • 1
    "Apart from that, even if we try to get a perfect model upfront, which I'm already convinced is very hard, requirements may change." I would like to add that you should not even try to get a (close to perfect) model up front. That might tie your mindset down to one type of solutions instead of keeping your options open.
    – Bent
    Commented Feb 22, 2016 at 13:01

3 Answers 3


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.

  • 1
    @Tibo you build the schema from scratch by running the same sequence of scripts. That's how you manage the problem. Given that as a standard you can get from any instance of the database - including one that doesn't exist yet - to a current schema and have confidence that its the same. There is no need to have two ways as per your example. (At least not given a consistent baseline - the first step is to establish the baseline and once you get to that baseline the problem goes away.)
    – Murph
    Commented Feb 22, 2016 at 14:43
  • 1
    Thumbs up for Alex' article; it may not be shorter, but it makes a much more practice-oriented and entertaining read.
    – Murphy
    Commented Mar 3, 2016 at 10:03
  • 1
    We're an Agile shop and we run a 100% uptime service and both of those apply to the DB as well. We migrate the production schema on average once a day and I would second everything Jan has said. One additional thing we do that has been invaluable is what we call migration testing, this runs as part of our build and deploy process. It takes a schema snapshot off production, applies any pending migrations from master to it and then runs the unit tests from the currently deployed production code against that schema. The goal is to check that applying the migrations won't break the running system. Commented Oct 4, 2016 at 8:38

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.

  • 1
    I really hope you oversized a problem of creating a new field in RDBMS table to make the statement more dramatic. The database table needs to be very special (or the new field type needs to be something exceptional) to really create a problem to add one field. Commented Feb 26, 2016 at 13:18
  • Yes, but it's never just one field ...
    – theMayer
    Commented Feb 26, 2016 at 13:44
  • 1
    I would say more often it is just one field. Dramatic schema changes aren't that often. I am not a fan of using RDBMSes with OO design due to impedance mismatch. However, adding new types (tables) and properties (columns) are relatively easy in both worlds although in NoSQL it is indeed a bit easier. But complex changes are pain in both cases. Even worse it becomes in event-sourced system with snapshots, in opposite how pleasurable the development experience for such system is. Commented Feb 26, 2016 at 14:12
  • I see that relational databases are often used as the "universal hammer" to solve data storage needs - when in fact there are very specific reasons to use them. In a carefully-contemplated system, one rarely has to worry about the issues I wrote about in my answer - I'm addressing at a more general audience who may not have have the experience to arrive at an appropriate system design up-front.
    – theMayer
    Commented Feb 29, 2016 at 17:12
  • There is no discrepancy between relational model and it does usually map to real world just as well as any other kind of model. Some operations will be easier with one kind and other with other kind. The trouble is when you create a model of one kind (object-oriented) and try to implement it with tools of another kind (relational). That does not work well. But real world is not object-oriented. It just is and you model it. And have to use the right tools for the selected kind of model.
    – Jan Hudec
    Commented Oct 4, 2016 at 19:13

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.

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

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

These steps apply to everything, not just databases.

  • The underlying scheme sometimes needs to be changed. As the application enters customer testing, new attributes crop up you've never heard of, attributes you thought were numbers turn out to be strings, relations you expected to be 1:1 turn out not to be so after all and so on. You can't cover this kind of things behind stored procedures (besides, stored procedures are part of the problem, because, like other things in the database, they don't live in version control).
    – Jan Hudec
    Commented Feb 22, 2016 at 10:14
  • @JanHudec since when do SPs not live in version control? You can cover such things, you change the SP API to take a string and write it to a different field, handling the old numbers and new strings in a bit of code in your SP. Not the nicest, but it can be better than going to every customer site to migrate their data to the new string format (there are better examples, but you get the idea). If the change turns out to be big, then you have to migrate , but at least with a DB API you have other, cheaper, options too.
    – gbjbaanb
    Commented Feb 22, 2016 at 10:26
  • You still have to go to each customer site to install the SP and add the new field. And when you are there, you can migrate the data too. SPs are useful in that they allow you to create backward compatible interface if you have multiple applications access the database, so you don't have to upgrade all of them at the same time. But they don't save any steps when the schema needs to change due to changing requirements.
    – Jan Hudec
    Commented Feb 22, 2016 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.