1 Reply Latest reply on Jul 13, 2015 11:01 PM by philmodjunk

    Contact table design

    LuisHernandez

      Title

      Contact table design

      Post

             
      1. FMP Version: 12
      2.      
      3. Operating System: Windows 7
      4.      
      5. I am designing the relationships for a database to manage contacts. Students are the primary contacts, but each student has a number of related contacts (local contact, foreign contact, authorized contact). Both students and contacts have contact information. For example,      
                    
        1. Student: FirstName, LastName, email, phone, address, city, state, zip, etc.
        2.           
        3. Contact: FirstName, LastName, email, phone, address, city, state, zip, etc.
        4.      
             
      6.      
      7. My question is, “what type of relationship should I have for these entities?”      
                    
        1. I have no need to send mass emails or post to any of these individuals.
        2.      
             
      8.      
      9. Should I:      
                    
        1. Put all of the information on one table (Student information, contact1 information, contact2 information, contact3 information), or
        2.           
        3. Separate the students and contacts: Student---<Contacts, or
        4.           
        5. create a people table and connect both students and contacts to it           
                           
          1. students---<people>---contacts
          2.           
                    
        6.      
             

       

        • 1. Re: Contact table design
          philmodjunk

          First, a. and b. can be two records in the same table. A self join or even a many to many self join can be used to establish the needed links between a student contact and other contacts. This enables you to link a given student to as many other contacts as is needed and with a "many to many self join", two students can link to the same contact--as might be the case with students that are siblings.

          Contacts----<ContactJoin>-----Contacts 2

          Contacts and Contacts 2 are two Tutorial: What are Table Occurrences? with the same data source table.

          Contacts::__pkContactID = ContactJoin::_fkContactID
          Contacts 2::__pkContactID = ContactJoin_fkLinkedContactID

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained