[danbruc]

TL;DR It is fundamentally impossible to find a solution that allows one to freely migrate any database schema and the data contained in that database forward and backward without any constraints. In practice you can push those constraints a bit around and most of the time you can hide them in dark corners where nobody looks and nobody gets hurt, but there is no silver bullet and there will never be one. (The last paragraph might be interesting, too.)

I completely agree with you but I was a bit unclear about what I wanted to say. What I really wanted to say is that the problem is fundamentally unsolvable in the general case no matter how hard you try. The pathological case is that from some point in time on you capture less information than you did before - only the full name instead of first and last name.

This is admittedly a relatively rare case in practice but nonetheless possible. Now there is fundamentally no way to migrate data captured past that point back before that point because you just don't have the necessary information and can not make it up out of thin air. As you mentioned it is not a big problem to migrate data captured before that point forward and backward by just keeping the additional information around and therefore making the migration step non-destructive.

In practice it should be a relatively rare case that you have to migrate data forward and backward repeatedly so the relevance of all that is not to high. Most of the time you start capturing additional information or stop capturing no longer relevant information and these cases behave much nicer under schema evolution. But especially when ever you change the way you capture some information you will from time to time get into trouble.

It is instructive to think of data migration in terms of mathematical functions. The function to migrate the data forward is always defined otherwise you could not perform the migration in the first place. But not every function has an inverse function and so you may not be able to migrate the data backwards. You can always make up a function that returns a tuple of the function value together with all arguments and therefore make inverting it trivial - this is exactly what also keeping first and last name around when you actually want to merge them into full name means - but as mentioned above this fails for new information where you only capture the function value and no longer the function arguments.