Converting bad table design to good design

[Guest]

I have acquired the wonderful task of redesigning a database that has been in
use for about 3 years. The person who designed the database did not normalize
the data, so I have one table with several columns that all represent the
same type of data (imagine a student schedule with a column for each class,
rather than a table to link the student and class tables).

What my problem is now is that I must either continue to work with the poor
design, or split the 48,000 or so records into maybe 200,000 records. What I
need to know is if there is a quick and painless way to do this. The analyzer
seems to only want to split out certain fields, and not the way I would want
it. Besides, like I said, it's one-to-many, but there are always 3 columns
for categories and 6 columns for reasons. Let me know if there is any more
information you would need to help me out on this one...

OLD TABLE:
Tracking # (key)
Employee (who dealt with the application)
Date
Customer Name
Category for review
Reason for review
Reason for review 2
Category for review 2
Reason for review 3
Reason for review 4
Category for review 3
Reason for review 5
Reason for review 6
Comments

NEW TABLES:
APPLICATION
Tracking # (key)
Employee (who dealt with the application)
Date
Customer Name

REASONS
Reason # (key)
Tracking #
Category for review
Reason for review

 

[Brendan Reynolds]

Sorry, I'm afraid the answer is no, there is no quick and painless way to do
it. The analyzer is, unfortunately, completely useless, don't waste your
time with it.

The only solution, in my experience, is to create the new schema and then
write a bunch of append queries to copy the data from the old schema into
the new one. Because of the amount of work involved in a) transferring the
data and b) modifying queries, forms, reports and code to recognize the new
schema, I've often had to do this kind of job in several stages, rather than
trying to fix everything at once.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thanks a bunch for the insight. One question if you get notified of replies:
I think we might go with a whole new database schema design, as well as new
queries, forms, reports, etc. All I would need to do would be to import the
data into the new schema. I have never used append queries. Where is a good
place to get information on them in regards to my problem?

 

[Brendan Reynolds]

There isn't really a whole lot to learn about append queries. Apart from the
fact that they append data from one table (or query) to another table (or
query) they are queries like any other. You design them in the same way as a
select query, except that in query design view you select Append Query from
the Query menu. You'll be prompted for the name of the table you want to
append to, and then an "Append To" row will be added to the query design
grid. You use this new row to specify which fields in the source table (or
calculated columns in the query) get appended to which fields in the target
table.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Cool. Thanks a million!

There isn't really a whole lot to learn about append queries. Apart from the
fact that they append data from one table (or query) to another table (or
query) they are queries like any other. You design them in the same way as a
select query, except that in query design view you select Append Query from
the Query menu. You'll be prompted for the name of the table you want to
append to, and then an "Append To" row will be added to the query design
grid. You use this new row to specify which fields in the source table (or
calculated columns in the query) get appended to which fields in the target
table.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.