redesign database help sought

Imagine a table named Customers the has various fields including; Country, State, City, ZIP.  In my redesign I want a separate table for each of those - Country table, State table, etc. that will be related to the new Customers table.  Naturally, I don't want to loose all of the data I have entered.  How do I get the old data structure into the new data structure?
Let me start with a simple (?) question (and this is where I have started).  Should I import the separate data into the new tables and work out a method for finding the correct relationship, or should I fill the new table data and build the relationship as I go?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First: What's the point of doing that? While I'm all for normalizing data, there are some things that really don't need to be broken out. For example, the likelihood of the US adding a new State anytime soon is slim to none, so there's no reason to break out a State field to it's own table.

Data should be normalized for a reason and a purpose. You'll find that breaking out all the Address components will often slow your application, since you'd have to join on several different tables just to present the user with Address choices.

With that said ...

You can pull all DISTINCT values from those columns and insert them into new tables quite easily:


And so on ...

Note this would not create a Primary Key field, or anything of that nature - it just moves the data from one table to another. You can add a PK field if needed, add indexes, etc etc.
Jeffrey CoachmanMIS LiasonCommented:
<quick aside, ...with no points wanted>

While I'm all for normalizing data, there are some things that really don't need to be broken out. For example, the likelihood of the US adding a new State anytime soon is slim to none, so there's no reason to break out a State field to it's own table.

The only reason I can think of to break the state info into a separate table is if you needed other State info.  For example:


So like LSM, I see no need for a separate State table in this case
DrftrBobAuthor Commented:
I have several reasons for wanting to normalize address data this way.
I somewhat want to do this for the exercise involved.  I want to, and think that I will have to, learn how to accomplish this type of task as I make similar changes in other structures.
Staying with address data, there are currently several tables with address data in them; customers, ship to, suppliers, bill to, etc.  I think I could simplify this to a Locations table, and I am planning on that anyway.  But, I am fond of the idea of only needing to look for a ZIP code, and if it's not found then it would be added to the combo.  That idea would follow down through city, state and country.  I have that worked out, and can do that for all new entries.  Thus my concern for my existing data.  Yes, I am concerned with the responsiveness of the database.  I don't anticipate it being a problem since there aren't that many records in the entire existing database.
As stated above, I'm not having trouble getting the data into new tables.  It's reestablishing a new valid structure that is testing me.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DrftrBobAuthor Commented:
If it helps, I have attached a file to a separate question in this category under: need to set a table field
Jeffrey CoachmanMIS LiasonCommented:
<I somewhat want to do this for the exercise involved.>
Then I would suggest something more practical...
For example, take a typical Customer table:
cID (PK)
cWorkEmail you can see here, in today's world, one person can have many "Contact Methods"
Normally this is all kept in one table for simplicity.
But in reality you should "Normalize" out the Contact methods, and add a Many-To-Many table

cmID (PK)

ccmID (PK)

This way you don't have to stop and add fields each time you need more contact methods for a client.

DrftrBobAuthor Commented:
I am having trouble with your example.  I don't see any common fields for a link.  And, I don't see why, since there is a tblContactMethod, that a tblCustomerContactMethods is needed.  I am a bit lost.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Because a "Contact Method" is not a "Method to Contact the Customer".  

Contact Mathods would hold data like this;

1  Email
2  Home Phone
3  Work Phone
4  Fax


Customer Contact Methods would hold data like this:

1    12
2    12   999-098-1234
3    13

So the first two contacts belong to Customer 12, and the last belongs to Customer 13

I'm not entirely sure what you mean by "reestablishing a new valid structure", but what I think you mean is how do you relate the new tables back to the old?

If so, you'd do this:

1) Create your new table which hold all of your Related Data. These tables would each have some form of "ID" field.
2) In your original tables, add a Column which will stored the new "ID" value
3) Now Update your original tables:

UPDATE MyTable SET MyNewIDField=LookupTable.IDField INNER JOIN LookupTable ON MyTable.TheOriginalField=LookupTable.TheValueField

LookupTable is the table you created in Step 1
TheOriginalField is the firled in the original table which held the non-normalized data
TheValueField is the field in the Lookup table which holds new ID value

But, I am fond of the idea of only needing to look for a ZIP code,
Zip codes will not allow you to lookup the full address data (like City and State, for example), since different cities can share the same zip code (there are two in my area).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrftrBobAuthor Commented:
I am ending my approach to this question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.