5 ms·

While all theory sounds nice in theory in practice it just fails. If you are only concerned with empty databases during development there is not much to be gain

by danbruc 13y ago

While all theory sounds nice in theory in practice it just fails. If you are only concerned with empty databases during development there is not much to be gained by keeping deltas of different database schema versions - just having one evolving database create script will do the job. Then just grab the version belonging to your application version and you are good to go. Once you have a non-empty database and have to provide data migration scripts everything becomes a one-way street in all but the most trivial cases because updates are destructive. My favorite toy example are firstname and lastname. If you decide to merge them into a single name column (without separator between firstname and lastname) you can not automatically go back because there is ambiguity when ever either firstname or lastname is missing or when at least one of firstname or lastname consists of two or more words. If you do the opposite - split the name into firstname and lastname - and somehow manage to come up with a satisfying heuristic how to perform the split, it is easy to go back by just merging firstname and lastname again. But then going forward again will not return you to the initial state in all the cases where your heuristic fails. Currently I think the way to go may be using a highly normalized database schema - like 5th or 6th normal form - and perform only non-destructive schema evolution. I am not yet sure how this will turn out in practice with respect to limitations imposed on the possibilities to evolve the schema and how bad it may become because of abandoned database schema artifacts and the contained data. Anyway I think it looks promising and after doing some more research, thinking and prototyping I will most likely give it a try in an upcoming project.

collyw 13y ago
Or you could just use a schema-less database like MongoDB </irony>
- zaidf 13y ago
  And introduce yourself to a new set of problems? Like the possibility of some part of the program saving first name and last name as one field and other parts as one field.
  - collyw 13y ago
    hence the </irony> at the end of my comment.
    - zaidf 13y ago
      woops :)
fendale 13y ago
I agree that rollbacks are hard, but if you think them through, there is usually a way. For your example, to merge first and last name, you would create a backup first, ie, create a table that contains the current version of first and last name, and if you need to go back, you restore. Now, if you have an application that cannot have any downtime through DB upgrades you could be in trouble - after your backup, more records could come in to the original table. Probably what you need to do is modify the application to start inserting both full_name and first_name, last_name, and then gradually move over all the old data to the new format etc - ie a gradual migration. Every application and example is different, but the problem is usually solvable, often with some amount of effort. The master schema create script is an idea I've seen in the past, and it works ok-ish up to a point. The problem is you need to update the master script, and also produce scripts to migrate production, so it doubles the effort. For enterprise database developers, I've seen plenty of development approaches that boil down to: * Create all changes on Dev using GUI tools. * Used TOAD to diff against production and produce delta script. * Apply delta to prod. Its gets the job done, mostly, so long as someone doesn't drop the dev database which is the only place with the new code - I've saw it happen. Then you have to go and beg the DBA to do a full restore just you get the release back on track!
- herge 13y ago
  I think you misunderstood what the OP was talking about. Have you ever looked at migration systems like South for django or the migrations in ruby on rails? He was saying how the reverse of a migration is night impossible, because what happens if you add a new user after applying that first name + last name migration, for which you don't have the split name.
  - couradical 13y ago
    That's why you apply the approach he was stating - you move slowly. Step 1. insert name column and populate from fn/ln. Step 2. Change code to point to name column as well as populate fn/ln Step 3. remove code pointing to fn/ln. Step 4. remove fn/ln columns. No loss of data, instant reversion at any point, because each step is non-destructive. It's just longer, harder to do in reality, but definitely the "right" way to do it if you are 100% concerned with data validity.

by danbruc 13y ago

collyw 13y ago

Or you could just use a schema-less database like MongoDB </irony>

zaidf 13y ago
And introduce yourself to a new set of problems? Like the possibility of some part of the program saving first name and last name as one field and other parts as one field.
- collyw 13y ago
  hence the </irony> at the end of my comment.
  - zaidf 13y ago
    woops :)

fendale 13y ago

I agree that rollbacks are hard, but if you think them through, there is usually a way. For your example, to merge first and last name, you would create a backup first, ie, create a table that contains the current version of first and last name, and if you need to go back, you restore. Now, if you have an application that cannot have any downtime through DB upgrades you could be in trouble - after your backup, more records could come in to the original table. Probably what you need to do is modify the application to start inserting both full_name and first_name, last_name, and then gradually move over all the old data to the new format etc - ie a gradual migration. Every application and example is different, but the problem is usually solvable, often with some amount of effort. The master schema create script is an idea I've seen in the past, and it works ok-ish up to a point. The problem is you need to update the master script, and also produce scripts to migrate production, so it doubles the effort. For enterprise database developers, I've seen plenty of development approaches that boil down to: * Create all changes on Dev using GUI tools. * Used TOAD to diff against production and produce delta script. * Apply delta to prod. Its gets the job done, mostly, so long as someone doesn't drop the dev database which is the only place with the new code - I've saw it happen. Then you have to go and beg the DBA to do a full restore just you get the release back on track!

herge 13y ago
I think you misunderstood what the OP was talking about. Have you ever looked at migration systems like South for django or the migrations in ruby on rails? He was saying how the reverse of a migration is night impossible, because what happens if you add a new user after applying that first name + last name migration, for which you don't have the split name.
- couradical 13y ago
  That's why you apply the approach he was stating - you move slowly. Step 1. insert name column and populate from fn/ln. Step 2. Change code to point to name column as well as populate fn/ln Step 3. remove code pointing to fn/ln. Step 4. remove fn/ln columns. No loss of data, instant reversion at any point, because each step is non-destructive. It's just longer, harder to do in reality, but definitely the "right" way to do it if you are 100% concerned with data validity.