Web Development



Updating a bad database

By MKS_Cheerful ·
I am working with an old Access Database where the creator did not know about normalization or using key fields. He just kept adding columns for each project to connect the line to from one table to then next. No key fields were used and all connections for reports were done in queries that had only indirect links. Every report was really done manually.


company table
company code --- made up field that is used as a indirect connection
main contact

They maybe 60 fields or more on some tables.

Any suggustions as to where to start untangling this mess?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Treat this as a Flat File

by Maevinn In reply to Updating a bad database

Build a model of what the system needs to do. Identify the full process for the data--input, formatting, outfput, storage, etc. Build the system, migrate the data in, treating it like a flat file.

It's time intensive, but saves loads of time down the road. Don't even try to salvage any of the structure--believe me, it will end up costing you WAY more time than just building anew.

Collapse -

Expensive idea

by MKS_Cheerful In reply to Treat this as a Flat File

I just took over this parts list for a Manufacturing company and the previous person didn't know much so it was all in flat files. The problem is that I have to get out reports based on some of the data in that mess and the previous person was very careful at checking off each item as done and remembering to enter the data in 3 to 5 places so that it linked. He manually set the links.

I am not that organized since I am a Software Engineer and not a Parts & Materials Engineer that I am replacing.

I tend to look at things very abstractly and not as brute force as I should be for this position. I have added indexes to most tables even if that doesn't always help. But now I can at lease relate two tables and still have edit access to the query that was not the case before.

I think I need more help in setting up Update Queries to move the data into a more normalized way.

I still can use more help.


Collapse -

Well I agree with

by Tony Hopkinson In reply to Expensive idea

Mavyn, start again is your best bet.

Incrementally fixing this snafu is going to be more expensive in terms of development time.

For an incremental fix, it's quite hard to advise you on where to start from this distance.

Probably you best bet is to do what you can, pick a table, do one normalisation, fix what this breaks.

Good first choices are final tables, ie ones that that aren't depended on. Once those are done, start on those that teh final tables depend on.. If something looks too scary, or it breaks horribly, undo it and go at it from another direction.

PS backups are you best friend in this situation.

As far as 'update queries', I'd recommend you create a new table and then write queries to fill it.
Once you are happy with it, rename the old one and the rename the new one to the expected name.

Insert into New_Parts Select ? From Parts
is the form of query to use.

Make sure every table has a column you can use as a primary key.

Do the changes in manageable lumps, one at a time in preference. Check for the restructure breaking anything before you do the next one.

Related Discussions

Related Forums