12 Replies Latest reply on Jun 28, 2011 4:17 PM by mgores

    re-doing an old contacts DB

    mgores

      Title

      re-doing an old contacts DB

      Post

      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.

        • 1. Re: re-doing an old contacts DB
          philmodjunk

          You definitely have some "grunt work" ahead of you to clean up discrepancies in your Company name data. There'll be no substitute for using a human brain to analyze and resolve many of them. You may find it easier and faster, however, if you set up a table view of your data and sort it by company name to try to pull similar company names together so that they are adjacent to one another. You may also find it useful to perform searches with wildcards to find a group of records that have slightly different spellings of the same company name.

          Your other questions clearly indicate why you need to upgrade to a set of related tables. If you can structure your relationships properly, most of these questions should be answered in the process of setting those up.

          would it then be better to have Addresses linked to Companies or to Contacts?

          You can do both. It doesn't have to be an either-or decision. You can link the same Address record to multiple contacts and to multiple companies. Sometimes it's useful to treat companies and people as two different types of contact record in the same table and other times it makes more sense to have two different tables, but either way they can be linked to the same table of address records.

          With your other two questions (Multiple addresses for the same contact or multiple ship to addresses for the same company), you can use a portal to display as many related address records as you need.

          • 2. Re: re-doing an old contacts DB
            mgores

            The grunt work is what I was not looking forward to.  The other parts are much more fun.

            So for each table I plan on a primary key, ContactID, CompanyID, etc

            Then Contacts::contactID=Phone::contactID

            Contacts::contactID=Address::contactID

            Companies::companyID=Contacts::companyID

            Companies::companyID=Address::companyID

            So if Jim Smith is a consultant that works for 2 companies I would enter him twice, once with each company.  That way he would have 2 contactID's with each related to the address for the appropiate company if he had officecs at each one, or both would be the same if he worked out of his own.

            • 3. Re: re-doing an old contacts DB
              philmodjunk

              There's no need to enter "Jim Smith" twice. You can link the same contact record to more than one company. Also consider that if you give Address records an AddressID, you can reverse the linkage to link a single address to more than one company or contact record.

              The trick is often to use a join table so that you can link one address record to many contact records and one contact record to many addresses:

              Contact----<Contact_Address>------Address

              Contact::ContactID = Contact_Address::ContactID
              Address::AddressID = Contact_Address::AddressID

              With such a relationship, you can place a portal to Contact_Address on a contact layout to list multiple addresses and a portal to Contact_Address on an Address layout to list multiple contacts with that address.

              • 4. Re: re-doing an old contacts DB
                mgores

                Ok.  So it would be possible to have a layout where Jim Smith shows up at his Boeing addresss when the company is Boeing and Northrop when the company is Northrop?

                • 5. Re: re-doing an old contacts DB
                  philmodjunk

                  It's possible. If you are using FileMaker 11, one method could be a filtered portal. Other options depend on how you structure your relationships.

                  • 6. Re: re-doing an old contacts DB
                    mgores

                    I am using FM 11 on a mostly Mac network.  There may be more and more PCs down the road if I can put together an accounting package someday.

                    As much as I've learned so far, that task seems pretty daunting still.

                    I think I understand how using a join table with those relationships should be able to handle the one contact/multiple company situations.  And having the addressID in both the Contact and Company tables should allow you to enter a new contact name - select a company - then select an address from addresses related to that company or enter a new one if needed.  The address with then be tied to the company and to that contact.  But no that won't work since that company record will already have an addressID from a previous address.........I don't know how that would work.

                    • 7. Re: re-doing an old contacts DB
                      philmodjunk

                      It would work the same as I illustrated with contacts and addresses:

                      Companies-----<Company_Address>-------Adresses

                      Companies::CompanyID = Company_Address::CompanyID
                      Addresses::CompanyID = Company_Address::CompanyID

                      This is one of the reasons I suggested you consider whether companies might be treated like just another kind of contact as this can simplify your relationships. (And you can still link contacts to companies even when both records are in the same table.)

                      • 8. Re: re-doing an old contacts DB
                        mgores

                        OK,  just took me a minute (and a heavy blunt object) to wrap my head around that join table and applying it there as well.

                        So there would be another table(s) with just the ID numbers, which I could use to bridge a company to a contact to an address?

                        • 9. Re: re-doing an old contacts DB
                          philmodjunk

                          Or even possibly a different occurrence of the same "join" table or your contacts table and your company table might even be the same table.

                          • 10. Re: re-doing an old contacts DB
                            mgores

                            So would it be easier to keep the company name field in the Contacts table and not have a Companies table?  And just have Contacts who have Addresses and Phonenumbers.

                            • 11. Re: re-doing an old contacts DB
                              philmodjunk

                              This isn't a question that has an easy answer, you'll have to weigh the trade off's and decide what works best for you and your database.

                              What you describe may work for you.

                              Another option is to consider a company to be one kind of contact, but structure your database so that yet another join table allows you to link contacts to each other. That would allow several "person" contacts to be linked to a common company (or more than one company) and a Company contact can list several "person" contacts.

                              And Companies can be a completely separate table. Take a look at what other data you need to record about a given company. If it's all just the same contact information as you record for a person, then one of the upper two options is likely all you need. If you have a lot of other data that you record just for companies and not for persons, then a separate table makes sense so that you don't have to record the same data over and over again for each contact from the same company. Keep in mind too that many companies have a phone number that is not specific to any one individual but reaches a receptionist instead. Likewise, I would think that a given company or department within a company could have several contacts but just one billing and/or shipping address.

                              • 12. Re: re-doing an old contacts DB
                                mgores

                                A first run through extracting companies and elimation of duplicates gets the number of companies down to 350, the number of contacts is ~4400.

                                Most companies have 1 billing address and sometimes more than 1 shipping address.  Some contacts have the same address as others at the same company but a specified "mail stop".

                                It was making sense to me to have a separate company table, then associate addresses to contacts and at least one main address to the company.