No system can possibly create datamigration scripts automatically from just the original and the final schema. There just isn't enough information.
Consider for example a new column. Should it just contain the default value? Or a value calculated from other fields/tables.
There is a good book about refactoring databases: http://www.amazon.com/Refactoring-Databases-Evolutionary-Addison-Wesley-Signature/dp/0321774515/ref=sr_1_1?ie=UTF8&qid=1300140045&sr=8-1
But there is little to no tool support for this kind of stuff.
I think the best thing you can do in advance:
Don't let anybody access the database but your application
If something else absolutely must access the db directly, give it a separate set of view specially for that purpose. This allows you to change your table structure by keeping at least the structure of what other systems see.
Have tons of tests. I just posted an article wich (with the upcoming 2nd and 3rd part) might help a little with this: http://blog.schauderhaft.de/2011/03/13/testing-databases-with-junit-and-hibernate-part-1-one-to-rule-them/
What you need is a JobStatus table with statustype and date. Then you can have a Status lookup table (statusId, statusName) with available statuses and a lookup table for JobTypeStatus with Jobtypeid and status ID as the fields (and perhaps start and end dates if they change frequently)that limits the statuses you can choose to only those available for that jobtype. You would use this to populate any drop downs on your application used to change status.
So then you have the job table (see below for possible renaming) and you join to the jobstatus to get the various statuses for that job. You can pivot to get them all on one row in the query or choose to do that in the application which is likely a better choice.
To keep old working code and add new structure, the most common thing to do is rename the Job table, then create a view named Job that is based on a query that gives the exact fields the old Job table had. There is a great book on refactoring databases that everyone shou dread who works with data, https://www.amazon.com/Refactoring-Databases-Evolutionary-paperback-Addison-Wesley/dp/0321774515
Yes, of course, they are not the same. I did not claim they were. I said:
> You can often recast that migration to something more like
By which I mean, in my experience you can usually write your migrations so that your code can work with the old AND the new version of the data, in which case you don't need to have a transaction around the whole operation.
Updating anything that's shared and mutable is a challenge. See Scott Ambler's "Refactoring Databases" https://www.amazon.com/Refactoring-Databases-Evolutionary-paperback-Addison-Wesley/dp/0321774515/ref=sr_1_fkmr1_4?ie=UTF8&qid=1521486064&sr=8-4-fkmr1&keywords=martin+fowler+signature+series
As an aside, doing some of this stuff is very complex in a running system. This [book](https://www.amazon.com/Refactoring-Databases-Evolutionary-paperback-Addison-Wesley/dp/0321774515/ref=sr_1_1?ie=UTF8&qid=1509110193&sr=8-1&keywords=refactoring+databases) may be of use to you
No system can possibly create datamigration scripts automatically from just the original and the final schema. There just isn't enough information.
Consider for example a new column. Should it just contain the default value? Or a value calculated from other fields/tables.
There is a good book about refactoring databases: http://www.amazon.com/Refactoring-Databases-Evolutionary-Addison-Wesley-Signature/dp/0321774515/ref=sr_1_1?ie=UTF8&qid=1300140045&sr=8-1
But there is little to no tool support for this kind of stuff.
I think the best thing you can do in advance:
What you need is a JobStatus table with statustype and date. Then you can have a Status lookup table (statusId, statusName) with available statuses and a lookup table for JobTypeStatus with Jobtypeid and status ID as the fields (and perhaps start and end dates if they change frequently)that limits the statuses you can choose to only those available for that jobtype. You would use this to populate any drop downs on your application used to change status.
So then you have the job table (see below for possible renaming) and you join to the jobstatus to get the various statuses for that job. You can pivot to get them all on one row in the query or choose to do that in the application which is likely a better choice.
To keep old working code and add new structure, the most common thing to do is rename the Job table, then create a view named Job that is based on a query that gives the exact fields the old Job table had. There is a great book on refactoring databases that everyone shou dread who works with data, https://www.amazon.com/Refactoring-Databases-Evolutionary-paperback-Addison-Wesley/dp/0321774515
> You can often recast that migration to something more like
By which I mean, in my experience you can usually write your migrations so that your code can work with the old AND the new version of the data, in which case you don't need to have a transaction around the whole operation.
This takes more work but is safer:
https://www.amazon.com/Refactoring-Databases-Evolutionary-pa...