re-doing an old contacts DB
I have a contacts DB that goes back to FM2 (possibly earlier). It is a single table with about 50 fields. I would like to make it a little more efficient and useful for other databases to interface with. I was thinking I need to break it up into different tables like Companies, Contacts, Addresses, Phone numbers. The old database had phone, home phone, fax, car phone, pager and main number. I added a contactID filed and I was able to make a new Phone number table and import each of the fields setting the phonenumber::type field after each import.
I was thinking that the easiest way is to keep the original table as the Contact table as that is the driver of the number of records in the original file. The hard part is extracting the Company table - since the Company names were all entered manually by whoever entered the contact, they are not necessarilly exact matches. Additionally not all of the contacts have complete information, missing addresses etc.
I'm thinking I have a lot of grunt work to do, coming up with the most correct company name for each company. going through each record (~4400) and getting it down to where I have consistent names for each company. From there I can import them to a new Company table - assign serial numbers, companyID - then import the companyID back to Contacts.
Am hoping someone may have a suggestion that would make this easier?
Also would it then be better to have Addresses linked to Companies or to Contacts? With some companies have multiple divisions, it is possible to contacts from the same company at 2 different address. Then some companies have multiple Ship to addresses but 1 Bill to address. I also have some contacts that work for more than 1 company.