improving a database (table re-design)

I've been asked to fix a database. I'm not an expert but
I do have enough experience to know what's wrong.

Currently this db has 3 tables

tbl 1 - Names, main address & contact info, categories,
other info (Christmas Card list, brochures, events etc...)

tbl 2 - regional addresses (some people will have 2 or 3
of these)

tbl 3 - departmental address (some people will have 1 of
these)

Some people in the db will have up to 5 addresses of 3
types. The previous designer couldn't get the
relationships quite right and so had to put the names and
titles from tbl 1 into tbl 2 and tbl 3.

What I want to do is create 3 tables:

tbl 1 - Names, titles, 2 categories (language & other)

tbl 2 - Other info - Christmas cards, brochures, events,
etc..

tbl 3 - Addresses/Contact info - with a column that
indicates category of address (necessary for queries)

This seems more logical and somewhat more normalized. I
have prepared a practice database with some sample data
and have perfected the tables and relationships.

Now, how do I go about migrating all the data from the
old DB to the new DB? Or, do I create new tables in the
old DB and manipulate the data into them? If so, how? Is
there a better way to do this? Re-entering all the data
can't be the only way!

Any ideas would be greatly appreciated.

You refer to the previous "designer". I think you mean, the previous
"person"! :-)

It sounds like you are going about the fix, correctly. Well done for
populating your new tables with some example data, before you import the old
data. Now you can be confident that the new structures are correct; or at
least, waaaay better than the previous ones!

As for importing the old data, you could:

1. add your new tables to the old db;
2. do whatever is necessary to get the data out from the old tables, into
the new ones, then
3. copy the (populated) new tables, to the new DB.

or:

1. in the new db, link to the old tables in the old db;
2. do whatever is necessary to get the data out from the old tables, into
the new ones, then
3. delete the links.

Whichever approach you choose, the issue is not whether the new tables
should be in the old db or the new db (initially). It is, how do you get
data from the old structures into the new ones? This will differ, depending
on what data the previous "designer", stored where. You *might* be able to
get away with simple UPDATE statements, to shuffle the data around. But
instead, you might have to write some VBA; along the lines of:

- read first record from old tables or a query joining the old tables;
- use string manipulation to extract the data for fields in the new
structure;
- add new record(s) to new table(s);
- repeat until all old data transferred.

HTH,
TC


"Christine" wrote in message
...
I've been asked to fix a database. I'm not an expert but
I do have enough experience to know what's wrong.

Currently this db has 3 tables

tbl 1 - Names, main address & contact info, categories,
other info (Christmas Card list, brochures, events etc...)

tbl 2 - regional addresses (some people will have 2 or 3
of these)

tbl 3 - departmental address (some people will have 1 of
these)

Some people in the db will have up to 5 addresses of 3
types. The previous designer couldn't get the
relationships quite right and so had to put the names and
titles from tbl 1 into tbl 2 and tbl 3.

What I want to do is create 3 tables:

tbl 1 - Names, titles, 2 categories (language & other)

tbl 2 - Other info - Christmas cards, brochures, events,
etc..

tbl 3 - Addresses/Contact info - with a column that
indicates category of address (necessary for queries)

This seems more logical and somewhat more normalized. I
have prepared a practice database with some sample data
and have perfected the tables and relationships.

Now, how do I go about migrating all the data from the
old DB to the new DB? Or, do I create new tables in the
old DB and manipulate the data into them? If so, how? Is
there a better way to do this? Re-entering all the data
can't be the only way!

Any ideas would be greatly appreciated.

Thanks for the help...I had a few ideas on how to do this
but it's always good to have some input. I will likely
have to go the VBA route. At least I have a lot of time
to do this.

-----Original Message-----
You refer to the previous "designer". I think you mean,
the previous
"person"! :-)

It sounds like you are going about the fix, correctly.
Well done for
populating your new tables with some example data,
before you import the old
data. Now you can be confident that the new structures
are correct; or at
least, waaaay better than the previous ones!

As for importing the old data, you could:

1. add your new tables to the old db;
2. do whatever is necessary to get the data out from the
old tables, into
the new ones, then
3. copy the (populated) new tables, to the new DB.

or:

1. in the new db, link to the old tables in the old db;
2. do whatever is necessary to get the data out from the
old tables, into
the new ones, then
3. delete the links.

Whichever approach you choose, the issue is not whether
the new tables
should be in the old db or the new db (initially). It
is, how do you get
data from the old structures into the new ones? This
will differ, depending
on what data the previous "designer", stored where. You
*might* be able to
get away with simple UPDATE statements, to shuffle the
data around. But
instead, you might have to write some VBA; along the
lines of:

- read first record from old tables or a query joining
the old tables;
- use string manipulation to extract the data for fields
in the new
structure;
- add new record(s) to new table(s);
- repeat until all old data transferred.

HTH,
TC


"Christine" wrote
in message
...
I've been asked to fix a database. I'm not an expert
but
I do have enough experience to know what's wrong.

Currently this db has 3 tables

tbl 1 - Names, main address & contact info, categories,
other info (Christmas Card list, brochures, events
etc...)

tbl 2 - regional addresses (some people will have 2 or
3
of these)

tbl 3 - departmental address (some people will have 1
of
these)

Some people in the db will have up to 5 addresses of 3
types. The previous designer couldn't get the
relationships quite right and so had to put the names
and
titles from tbl 1 into tbl 2 and tbl 3.

What I want to do is create 3 tables:

tbl 1 - Names, titles, 2 categories (language & other)

tbl 2 - Other info - Christmas cards, brochures,
events,
etc..

tbl 3 - Addresses/Contact info - with a column that
indicates category of address (necessary for queries)

This seems more logical and somewhat more normalized. I
have prepared a practice database with some sample data
and have perfected the tables and relationships.

Now, how do I go about migrating all the data from the
old DB to the new DB? Or, do I create new tables in the
old DB and manipulate the data into them? If so, how?
Is
there a better way to do this? Re-entering all the data
can't be the only way!

Any ideas would be greatly appreciated.



.