I've been developing a web site with a friend for about the past 8 months. We've gone through a few major schema changes. This has been the approach we've followed when moving to a new schema and it has worked very well.
When we're ready to go live on a new schema, I'll compare the old schema to the new one and see where the tables differ. From that, I generate SQL statements to copy the data from the old schema to the new schema (I use quite a bit of perl code through all of this). We're using SQL Server so we can copy data directly from one schema to another with SQL statements. If I couldn't do it this way, I'd probably use perl to manage the copying.
A few days before we go live, we'll create a new database, load the new schema, test the conversion scripts and do testing on the new site. If all is well, we'll create another new database and do it all again to go live.
Most of our schema changes are due to new features being added, not design problems (yeah, that's what they all say :-).
At my last job, we tried using 'ALTER TABLE' commands to maintain a database. This didn't work too well. Sometimes the changes were too involved to use 'ALTER TABLE' and tables would have to get recreated. This presented problems with tracking foreign key constraints. Besides, if you get something wrong in this process, you only have a database dump to fall back on (you do backup your database before changing the schema, right?). With the method described above, all of the old data is still intact in the old schema.
|90% of every Perl application is already written. ⇒|
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.
| & || & |
| < || < |
| > || > |
| [ || [ |
| ] || ] ||