Upgrading to MySQL Backend.

[Crilen007]

I know the basics, I have created a sample database, but I have a question..

I took over this place, and the databases that were created do not follow proper database structure.

There is redundant data everywhere.. its pathetic.

Now, the main database, that is used daily is a database of employees.

Now, if it was as simple as converting the structure to mysql and distributing a new database to the users, I would be done a long time ago. But this one is in use all the time. Many database link to it.

The database itself needs a redesign also, a major one...

So, how would I go about creating a new one, while the users can still enter data and use it?

1) Make a read only version, and get them to keep track of their changes on paper till the new one is ready for editing?

2) Keep track of what changes I made to the table structure, and when the new one is ready 100%, just them both down, convert the table structure AGAIN from the old one (since it would be done once for the sample one).

3) Create the new database emtpy, but with room for each of the fields that were required from the old database. When the time comes, cut the users off, and insert the data from the old DB into the new one?

4) Come in on the weekend and do it when perhaps most people aren't on the database?

5) Maybe there is a simple thing that I do not know about? To keep track of what was migrated and how, so I can just run the queries again to update the needed tables when the time comes, and have it done instantly instead of taking hours/days?


Any input would be appreciated, and welcomed.

It would be the first time I would have to do something this big to a database that is used all the time.

 

[Pat Hartman]

Welcome to the world of production systems You cannot modify their structure while the users are using them and you can't mess with their data.

You need to create a complete test environment and copy everything into it. Make the modifications. Test the applications. Then on the weekend or in the evening when everyone else is gone, back up the existing applications in case you need to revert to them, replace the front ends with your modified databases, and convert the data. Test again.

Some of my clients have as many as SEVEN levels of testing environments and the application moves in order through each of them as it gets modified and tested. When everyone finally signs off, it gets moved to "production" and the tables get converted if necessary.

 

[Crilen007]

Another problem is... a lot of other databases link to this one. So once it gets approved, I would have to convert the other ones before anyone can use them.

 

[Pat Hartman]

I see a lot of long hours and weekends in your future.

Actually the great thing about RDBMS' is that you can make some changes without actually impacting existing applications. If you are adding fields there is no need to change the other linked applications unless they need access to the new data. If you are increasing field sizes that should not affect other applications although this needs to be verified. Make sure that any variables used in VBA are not going to cause a problem.