Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: OT: Ways of managing changes to a database's structure during a project.

by gmax (Abbot)
on Feb 04, 2004 at 11:16 UTC ( #326464=note: print w/ replies, xml ) Need Help??


in reply to OT: Ways of managing changes to a database's structure during a project.

I would go for your first solution, transfering data from a database to another. And there are several reasons for that:

  • Having a new database created and filled with data, you can actually test your data with the existing code before switching.

  • With the other approaches, if an error occurs, it is more difficult to roll back to your initial stage.

On a side note, even though I know that it may be late for you, I would say that changing data structures during development could be a symptom of a weak design. Perhaps spending more time at the design desk would save you all this ordeal.

 _  _ _  _  
(_|| | |(_|><
 _|   


Comment on Re: OT: Ways of managing changes to a database's structure during a project.
Re: Re: OT: Ways of managing changes to a database's structure during a project.
by EvdB (Deacon) on Feb 04, 2004 at 11:27 UTC
    I hear what you are saying about the testing - it is a very valid point.

    As for the getting it right at the design stage I agree, but it is not always possible or even desirable. I happen to have very few changes to make but a change is a change and requires respect - hence the OP.

    Changes can occur for lots of reasons, usually they are used to add features. For example say I am ( and I really am ) designing a small simple web shop. After a few months of trading it turns out that discounts would be a good thing for customers spending over a certain amount. To accomodate this the database (and code) needs to be changed. This sort of thing cannot always be spotted at the initial design stage and it would be impossible to take into account all the possible future features that are needed.

    In summary: Yes, getting it right at the start is a good thing, but so is making changes based on lessons learnt.

    --tidiness is the memory loss of environmental mnemonics

      After a few months of trading it turns out that discounts would be a good thing for customers spending over a certain amount. To accomodate this the database (and code) needs to be changed.

      Actually, I would think that for adding such functionality you would only need to add to a DB, not actually modify the existing structure. (I dont consider table additions to be a modification.) Perhaps your tables weren't sufficiently normalized? ;-) (Code changes would for sure be required.)

      Anyway, snarkyness aside, my approach with stuff like this tends to be to write a perl script that does the DB modification via SQL. I test it on copies of the production DB, and then once happy and the code is approved for production run it on the production DB. I like to use the perl script to run the SQL because I find that its usually the easiest way to ensure newly added fields have the appropriate values assigned to them. For instance, ill use alter table to add a field as nullable, walk the table updating each record so it has the correct value, then alter table again to set it not-nullable, and perhaps add an index on the new columns.

      Anyway, good luck.


      ---
      demerphq

        First they ignore you, then they laugh at you, then they fight you, then you win.
        -- Gandhi


Re: Re: OT: Ways of managing changes to a database's structure during a project.
by pfaut (Priest) on Feb 05, 2004 at 14:51 UTC

    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.
    dragonchild

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://326464]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (14)
As of 2014-08-27 13:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (238 votes), past polls