If you absolutely have to be in a spot to support this a trigger-based approach is by far the best way to handle it, either by pervasive use of database views (and have INSERT triggers to handle writes, that way you can version the view and handle any extra validation logic during migration with a temporary performance hit) or having a trigger on the old table feeding the new schema and then swap the tables upon upgrade of the software.
Both solutions suck in some way compared to the downtime required schema changes as far as simplicity is concerned, but they let you maintain consistency throughout the change at least and let you bail out midway if needed
On that note, I'm glad all but a single system I maintain is only used by employees - just schedule a maintenance window after-hours and I'm free to take things down for a couple hours if needed.
Comments
If you absolutely have to be in a spot to support this a trigger-based approach is by far the best way to handle it, either by pervasive use of database views (and have INSERT triggers to handle writes, that way you can version the view and handle any extra validation logic during migration with a temporary performance hit) or having a trigger on the old table feeding the new schema and then swap the tables upon upgrade of the software.
Both solutions suck in some way compared to the downtime required schema changes as far as simplicity is concerned, but they let you maintain consistency throughout the change at least and let you bail out midway if needed
On that note, I'm glad all but a single system I maintain is only used by employees - just schedule a maintenance window after-hours and I'm free to take things down for a couple hours if needed.