How to handle a change of design

[Michael Wong]

Hi,

I have developed an Access database for my company which has used it since
more than a year.
Till now, nothing big has changed in the table design. Only for the forms
and queries which get changed as I'm learning new methods of programming.

Now, I'm curious about how people would handle a change in their database
design, mostly table design. Like a GST which has been added to Australia,
or simpler, new way of doing things in the company.

Anyway, just for curiosity.

Thanks

 

[Rose]

Are you talking about a seriously radical change, or just
adding a new table and the necessary relationships? Or are
they changing their forms to do things very differently?

I'd say it was up to you. Whatever involves less work is
good in my book. If it makes sense to just tweak what
you have, fabulous and probably better for everyone. If
tweaking it is going to involve a lot of complicated mess,
and you would rather save yourself the headache and start
from scratch in your design, go for it. It completely
depends on how complex your db is. Myself, for your average
db, can't imagine a change in procedure being so
complicated that I do more than change some forms and add
fields to my tables and queries.

 

[John Vinson]

Now, I'm curious about how people would handle a change in their database
design, mostly table design. Like a GST which has been added to Australia,
or simpler, new way of doing things in the company.

With pain, dismay, gnashing of teeth (and probably a fairly large
number of billable hours).

Depending on the nature of the change to the tables, you will need to
make small or large changes to all of the Queries, Forms, Reports,
combo boxes, etc. which depend upon that table. If the change involves
(say) splitting a non-normalized table into two or more normalized
tables, it may be necessary to rebuild some of these objects
completely; if it's just adding a field, it may be much easier, but
still can be tricky (to make sure you add the field everywhere it's
needed).

If the changes are substantial, I'd be inclined to copy the database
(backend and frontend) to a development copy; work out the changes
thoroughly; test, test, test; get your most computer savvy user to
test, test, test; get your LEAST computer savvy user to test, test,
test; and then redo the changes in the production system.

 

[Michael Wong]

Thank you for your reply, I knew it will not be so easy, even for the
smallest change...

What about for a radical changes? Do the data get changed and imported to
the new database? Or just start from scratch, even for the data.

 

[John Nurick]

Hi Michael,

If you followed good design practice when you created the original
database, it will be possible to transform the old data into the new
structure with a series of queries.

Even if the original is not well structured, it's almost always possible
to transfer most if not all the data that way.

 

[Michael Wong]

Thank you John for you reply,

I guess I'll just stick to my design for the moment and see when changes are
required.

 

[John Vinson]

What about for a radical changes? Do the data get changed and imported to
the new database? Or just start from scratch, even for the data.

If it's at ALL possible - and it almost always would be - I'd salvage
the data. I've even put several dozen Append, Update and Delete query
names into a table and written code to run the multiple queries in
sequence.