Results 1 to 2 of 2
2001-10-28, 22:29 #1
- Join Date
- Jan 2001
- Portland, Maine, USA
- Thanked 0 Times in 0 Posts
Move data to normalized tbls from 1 massive tbl (97/SR-2)
Could anyone explain to me how to append data to newly normalized tables from one massive mess of a table. I have a table that should be split into about 4-5 different tables.
The non-normalized table has about 1500 records of customers that have taken out mortgages with an agency. Some customers have multiple mortgages. They have dealt with multiple mortgages for the same customer like this. Last name field: "Smith" for first mortgage, Smith2 next mortgage, Smith 3 and so on.
I did a search and found a great post by Charlotte, post # 60828, but still can't get it through my thick skull! Does anyone else have any suggestion to give to me on how to go about thinking about it and then actually doing it. Even if I could get pointed to a good site or article that explains just this would be great.
I'm tired of avoiding jobs with databases that have non-normalized tables. I need to start facing my fears! <img src=/S/devil.gif border=0 alt=devil width=15 height=15>
Thanks to all that could help. I REALLY would appreciate it.
2001-10-29, 01:40 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: Move data to normalized tbls from 1 massive tbl (97/SR-2)
I don't recall the specific post, but you basically have to add fields to the non-normalized table (let's call it tblMess) to hold keys for records in the normalized table.
For example, you might add a CustomerID field to the tblMess. Then you would create unique customer records for only the first instance of customer Smith in the table that holds customer records. Then you would do an update query on tblMess to insert the customerID values from tblCustomer into all the related fields in tblMess, including Smith2, etc., for the same address.
You would do the same thing to populate the other tables as well, but you have to do them in a logical order. If the phone number table has CustomerID as a foreign key, then you have to use the CustomerID from tblMess when you create the phone record. Then when you update tblMess with the PhoneID value, you use CustomerID and phone number to link the records so you can update the PhoneID field in tblMess from the PhoneID field in tblPhone.
You're going to want to create the base records first. So if customer is the entity that everything else relates to, you need to populate the Customer table and update those keys in tblMess first before you populate other tables.
At every step of the proceedings, you will want to look at the records in tblMess and make sure they make sense. If you sort the records by customer last name, first name and then by address, you should be able to see whether you've got the appropriate customerID in all the various Smiths at the same address, for instance.
I've never found a clean, one-time-through method for doing this. It takes a lot of hand manipulation and personal judgement to decide if those really are the same person and how to handle "almost" duplicates.Charlotte