14 Replies Latest reply on Mar 7, 2009 11:04 PM by comment_1

    New - Fundamental Design question - foreign keys

    randy

      Title

      New - Fundamental Design question - foreign keys

      Post

      New to FM, have bought FM Pro 10 Missing Manual (read chapter 7) and White Paper for FMP Novices.  Attempting to make Entity Relationship Diagram.  Still unclear on question of foreign keys.

       

      As I understand it, every table must have a unique primary key field.  In a 1-many relationship the many table needs a foreign key for the 1 side.  Apparently the 1 side never need a foreign key for the many table?

       

      What about a 1-1 relationship?  Is a foreign key necessary on either (or both) side(s)?  Or should not exist on either side?

       

      The White Paper is way too advanced beyond such a basic question, and having re-read the Missing Manual Chapter 3 times, I am still unclear on this.  Any help would be appreciated.  Thanks. 

        • 1. Re: New - Fundamental Design question - foreign keys
          davidhead
            

          First of all, you will not be making an ERD in FileMaker Pro. There is no place to do so. You will be referring to the relationship graph which is not and ERD for a number of reasons.

           

          You are correct in your second paragraph about primary and foreign keys.

           

          In a 1:1 relationship, one side will always be the parent and the other the child. The foreign key goes on the child side. The parent will be considered the record that exists even if the child does not. And a child side record would never exist without a parent.

           

          For example, say you have a contact table and a staff details table. There is a 1:1 relationship there. Contacts who are staff will have a staff record. Contacts who are not staff will not have a staff record. A staff record will never exist without a contact record to attach it to.

           

          Does that make sense? 

          • 2. Re: New - Fundamental Design question - foreign keys
            randy
              

            David, thank you very much for your quick reply.

              

            As this is my very first attempt to create a database I should have specified that I am using a word processor to prepare my ERD drafts.  I'm trying to follow the consistent advice in all the materials I've seen and work out the ERD completely before starting work in FileMaker.

             

            Very much appreciate you verifying that I have the foreign keys conceptualized correctly for 1-many.  Your parent-child explanation is very clear also.  What about 1-1 relationships where there is ALWAYS a record on both sides?

             

            For example, I am trying to design/diagram a database for a 2-way radio repeater system.  There are USERS, who are persons who sign contracts to use the repeater.  Each USER must have an FCC License (CALL SIGNS::Call_Sign and Expiration_Date).  USERS may also have OPERATORS, who are immediate members of the family living in the same household (and therefore, under FCC rules, entitled to utilize the USER's FCC License and Call Sign).

             

            USERS-->OPERATORS is 1-many.  USERS-->CALL SIGNS is 1-1, but not parent-child since each USER must have a Call_Sign.  Moreover, OPERATORS may have their own separate FCC Call Signs.   OPERATORS-->CALL SIGNS is also 1-1, but parent-child, as OPERATORS need not have a Call_Sign (as they can utilize their USER's Call_Sign).  Therefore, as I understand your parent-child explanation, the field:

             

            _k_f_Operator_Serial_Number

             

            goes into the CALL SIGNS table.

             

            FCC Call Signs are entities that will be searched frequently (heard on the air for example).  Expiration_Date is also an attribute that will be searched frequently.  I assume a separate CALL SIGNS table is desireable rather than just putting Call_Sign (and other related) fields into USERS and OPERATORS since Call Signs and Expiration Dates will need extensive reporting and most OPERATORS will not have a Call_Sign?

             

            Since USERS-->CALL SIGNS is 1-1, but NOT parent-child (Call_Sign is MANDATORY for every USER), is there a foreign key necessary for this relationship?

             

            Sorry for the lengthy message.  I also reviewed Chapter 4 of the FM Pro 10 User's Guide, but in the materials I have read so far, my question is either too detailed or, with the White Paper, too basic.  Hopefully mastering these concepts now will save me a lot of time and trouble in the future.  (My next effort will be considerably more complex - a database for the 2-way radio club.)  Thanks again for your attention and time.

            • 3. Re: New - Fundamental Design question - foreign keys
              davidhead
                

              OK, I am trying to work out what is needed here. There is one area where I am a bit confused.

               

              FCC Licenses are issued to Users. You note a license has a call sign and an expiry date. So do you need a license to get a call sign? In which case, operators under a user's contract will be separately licensed. And I suspect that would mean that a license and a call sign is really the same entity. 

               

              Is this the right thinking?

               

              It may also be that users and operators are actually the same entity - people. All may hold licenses (or not) and they may be linked to a contract or to other people for the use of a contract. This may sound more complicated but it may be a better structure.

               

              All in all, you are correct in trying to get this right in an ERD before building the database. So keep going! 

              • 4. Re: New - Fundamental Design question - foreign keys
                comment_1
                  

                Randy wrote:

                What about 1-1 relationships where there is ALWAYS a record on both sides?


                Usually (and it's hard for me to come up with an example of an exception), this would not be a relationship at all, but a single entity with additional attributes. In your case, the Users table would simply have a CallSign field (however, the expiration date suggests that one user may have many licenses or license terms for his/hers one call sign).

                 

                 

                BTW, David's rule of where the place the foreign key in a 1:1 relationship is a good rule-of-thumb - but no more than that. There are other considerations affecting the choice. For example, take a Quotes - Invoices situation: each invoice has a quote, but not all quotes result in an invoice. Here, it might be better to place the InvoiceID foreign key into Quotes, because it marks quotes as invoiced, and searching for uninvoiced quotes will be faster when using a local field.


                • 5. Re: New - Fundamental Design question - foreign keys
                  randy
                    

                  David - You are correct, when an FCC license is issued the call sign is a part of the license.  The call sign must be announced over the air when transmitting and is evidence of the license.  You are probably familiar with call signs from radio and tv stations (WABC, KXYZ, etc.).  Yes, a license and a call sign are really the same entity, sorry for the confusion.

                   

                  Users and Operators are NOT the same.  A User has signed a written contract to utilize the repeater station (as required by the FCC Rules and Regulations).  Operators have NOT signed a contract, but, utilize the repeater station under their USER's contract.  If the USER's contract right is suspended or revoked none of the underlying Operators may utilize the repeater either.  USERS MUST have an FCC License (Call_Sign), but Operators need not have one since they can use their USER's FCC License (Call_Sign).  Things get complicated because an Operator (18 or older) may get their own separate (different) License (& Call_Sign) if they wish.  But an Operator still utilizes the repeater station only through their User's contract.

                   

                  Perhaps an example will help.  User_K signs a contract to use the repeater.  His FCC license (Call_Sign_A) is used by him and members of his family in the same household (Operators).  His wife, Operator_M, uses Call_Sign_A.  However, his adult son, Operator_J, decides to get his own separate license, Call_Sign_Z.  Operator_J still uses the repeater under the contract of User_K, but identifies on the air with Call_Sign_Z.

                   

                  Now Operator_J moves out of the house.  He is no longer entitled to utilize the repeater under the contract of User_K, since he is not living the in the same household.  Operator_J must now become a full fledged USER and sign his own separate contract.  Does this make any sense?  Its necessary because of the FCC Rules and Regulations.

                   

                  USERS must have their own Call_Sign (non parent-child, every record has a Call_Sign entry in both USERS and CALL SIGNS tables).  Operators need not have a Call_Sign, but may decide to get one of their own (parent-child).

                   

                   Would a current draft of my ERD help to answer my question regarding 1-1 non parent-child (USERS-->CALL SIGNS) foreign key?  Thanks again for your generous time and attention.

                  • 6. Re: New - Fundamental Design question - foreign keys
                    randy
                      

                    comment - Thank you very much for your reply.  It occurred to me that Call_Sign, Expiration_Date, Issue_Date, FRN, etc. could just be put into USERS and OPERATORS tables, thus obviating the need for a CALL SIGNS table altogether.  Two things immediately caused me concern with such an approach.

                     

                    First - I anticipate much reporting relating to call signs.  Any call sign heard over the air should be able to be quickly checked to see if its authorized on the repeater, and, if so, who is it and related details.  I would like to automatically generate reminder letters and/or e-mails to any USER or OPERATOR x number of days before their FCC license reaches Expiration_Date.   A letter suspending a USERS' (and all related OPERATORS') operating rights should be automatically generated if Todays_Date is greater than Expiration_Date, etc.

                     

                    Second - Most Operators will not have a Call_Sign.  Its an unusual exception rather than the rule.  This means a lot of empty fields in the OPERATORS table.

                     

                    Would having a separate CALL SIGNS table better facilitate rapid and efficient reporting along the lines as above?? 

                     

                     I am struggling to understand your Invoices/Quotes example.  Its obviously the opposite from David's example where the table with an entry in every record is the parent and the table with some missing entries is the child with the foreign key.  I think I need to better understand the function/purpose of a foreign key.  Also, why is a foreign key not required on both sides of every relationship.  That would result in a foreign key opposite of every primary key?  I understand that in 1-many only one foreign key in required in the many side, for example.

                     

                    Thank you very much for your assistance with such a basic concept.

                    • 7. Re: New - Fundamental Design question - foreign keys
                      comment_1
                        

                      My point is this: what is entity and what is an attribute is largely a business decision. Do I track users or do I track their call signs? My impression is that you need to track users only, and that their call signs are a matter between them and the FCC.


                      Randy wrote:
                      Most Operators will not have a Call_Sign.  Its an unusual exception rather than the rule.  This means a lot of empty fields in the OPERATORS table.

                      That would be a consideration only if you decide to put operators and users in the same table (something worth considering). Otherwise, operators with their own license are also users in their own right, and deserve an entry in the users table.

                      In any case, empty entries are perfectly valid entries, and by themselves not a reason to create another table.



                      Randy wrote:
                      Also, why is a foreign key not required on both sides of every relationship.

                      Because it would be redundant, and potentially conflicting. A 1:1 relationship is merely a special case of a 1:n relationship, where n happens to be 1. Theoretically, any one of the two 1's in 1:1 could be seen as representing the "original" n.


                      • 8. Re: New - Fundamental Design question - foreign keys
                        davidhead
                          

                        OK, so I would identify LICENSE as an entity that has attributes such as call sign, expiry date, etc.

                         

                        I understand that the definition of a user and an operator is not the same. However, in terms of entities they ARE the same - they are all people (in your system at least). The difference between these people is that some have contracts and therefore are called users; others operate under another person's contract and are called operators.

                         

                        People with contracts have to have licenses.

                        Some people have licenses but operate under another person's contract.

                        All users are operators but not all operators are users.

                         

                        So the PERSON entity has a 1:0 or 1:1 to a CONTRACT.

                        And the PERSON entity has a 1:0 or 1:1 to a LICENSE.

                         

                        So the CONTRACT entity has a foreign key for a person. As does the LICENSE entity. 

                         

                        The PERSON entity also has a foreign key for a self relationship to link a person record to another person record that holds a contract.

                         

                        Using your example, you could see why this is useful. 

                         

                        <i>User_K signs a contract to use the repeater.  His FCC license (Call_Sign_A) is used by him and members of his family in the same household (Operators).  His wife, Operator_M, uses Call_Sign_A. There is also an adult son, Operator_J.</i>

                          

                        A person record is created for K.

                        A related license record is created for K.

                        A related contract record is created for K.

                        M is added as a person in the same household so is linked to K (as the holder of a contract). 

                        J is added as a person in the same household so is linked to K (as the holder of a contract).

                         

                        <i>However, his adult son, Operator_J, decides to get his own separate license, Call_Sign_Z.  Operator_J still uses the repeater under the contract of User_K, but identifies on the air with Call_Sign_Z. </i>

                         

                        A related license record is added for J.

                         

                        <i>Now Operator_J moves out of the house.  He is no longer entitled to utilize the repeater under the contract of User_K, since he is not living the in the same household.  Operator_J must now become a full fledged USER and sign his own separate contract.</i>

                         

                        The link from person J to person K is deleted.

                        A related contract record is created for J.

                         
                        If we look at the license records, we will be able to easily search for and see who 'owns' each call sign. 
                        If we look at contract records, we would be able to see who was the owner of the contract and who is allowed to use that contract right to the repeater. We could even see the call sign of each person. 

                         

                        Does that all make sense? 
                        • 9. Re: New - Fundamental Design question - foreign keys
                          randy
                            

                          comment - While I was writing my reply to David you posted your message, and thus you did not have the benefit of my more detailed explanation.  As an aside, it is my responsibility as the licensee of the repeater station to track Call Signs and assure that no unlicensed use of the repeater occurs.  Therefore I do need to track call signs carefully in addition to USERS and OPERATORS.

                           

                          I can have USERS and OPERATORS in the same table, however, OPERATORS will not need most of the contact information, address, phones, etc.  since its the same as their related USER living in the same household.

                           

                          I understand what a primary key is and that its created for every table.  I understand that a foreign key is one that corresponds to a primary key in another table.  What I have not yet been able to grasp is when and in which tables foreign keys are placed or not, and why.  If I followed your last point correctly, 1-1 relationships are really just a special form of 1-many relationships?

                           

                          Thanks for your additional information, and sorry to be so slow on the uptake. 

                          • 10. Re: New - Fundamental Design question - foreign keys
                            randy
                              

                            David - Thanks so much for your thoughtful analysis of my design problem!  It all makes good sense to me, although I am not yet sure exactly how to make the result all work in reports as you describe.  I will study reports further in my Missing Manual Book.

                             

                            Since Persons who are Operators will NOT need much of the contact information (address, phones, etc) in their records (because its the same as their related User), would I then build in this contact information in the CONTRACTS table?  Therefore only Persons with Contracts would have these fields?  And Persons without contracts would pull this information from their related Person's Contract record?

                             

                            "The PERSON entity also has a foreign key for a self relationship to link a person record to another person record that holds a contract."

                             

                            I understand the concept, but I'm not sure exactly how to implement this, where would you recommend I read to get up to speed on this concept?

                             

                            Thanks again for taking the time to sift through all my explanation and provide the design solution!

                            • 11. Re: New - Fundamental Design question - foreign keys
                              comment_1
                                

                              IIUC, you need to track the call signs and licenses of your clients - not call signs and licenses as such. There is a big difference between the two.

                              To give a (hopefully) clearer example: say a company has employees, and in the Employees table there is a field for the employee's car registration number. This is used at the factory gate to let employees in. Other than that, we are not interested in any way in the employees' cars, and thus have no use for a table of Cars. However, the situation would be very different if these were company cars issued to the employees. Then we'd definitely want to track cars as such, and we'd need a Cars table for this.

                              (I had a similar example using an employee's spouse, but the idea of company-issued spouses didn't play to well...::P)

                               

                               


                              Randy wrote:

                              If I followed your last point correctly, 1-1 relationships are really just a special form of 1-many relationships?


                              Yes. And in a one-to-many, there's no question of where the foreign key goes: a child knows who its parent is, but the parent is ignorant of its children. This way, the foreign key field holds a single value (ParentID) - which would not be possible if the parent had to remember all of its children primary keys.



                              • 12. Re: New - Fundamental Design question - foreign keys
                                randy
                                  

                                comment - I gather your company cars example means that in my case the FCC License fields are better off as attributes in the USERS and OPERATORS Tables since licenses are incidental and not primary activities of my repeater system?  Company issued spouses is an intriguing idea, but I digress ...   ha ha 

                                 

                                 I follow where the foreign key goes in a 1-1 when not every record has an entry on both sides.  I am just not sure how to figure out what happens when a 1-1 ALWAYS has entries on both sides.  Thanks for your further elucidation!   

                                • 13. Re: New - Fundamental Design question - foreign keys
                                  randy
                                    

                                  OK, I have just re-read (for the umpteenth time) page 313 of my FM Pro 10 Missing Manual book.  I now have re-clarified why the foreign key in a 1-many goes in the many table.

                                   

                                  If I am getting this right, then in any relationship where 1-0 might ever happen, the foreign key should be put into the 0 side.  In a 1-1 where there is ALWAYS a record on both sides it doesn't matter which way you do it?  (I assume any design where 0-0 could ever happen is fatally flawed?)

                                   

                                  Thanks for your patience everyone!

                                  • 14. Re: New - Fundamental Design question - foreign keys
                                    comment_1
                                       Well, the way I would put it is that the only interest you have in licenses is insofar as they describe the people you track. Therefore they are (for you) an attribute of users/operators.

                                    However, I must state two reservations, hoping not to further confuse the issue :

                                    1. The fact that something is merely an attribute and not an entity does not a necessarily mean it won't have a table of its own. For example, you might create a table just for phone numbers, because one person can have many of them.

                                    2. Having separate tables for Users and Operators, each with its own license fields, would be an extremely bad idea, given what you said earlier. Just imagine the person at the gate having to check 2 or 3 lists of cars before admitting anyone. If for some reason you'll find that users and operators cannot be in the same table, then you should definitely have a third table for call signs/licenses, common to both types.

                                    (In fact, you could put ALL their common attributes, such as name, address, phone number, etc. in this table, thus implementing a so-called supertype/subtype data model - but this is not something I would recommend to a beginner to try on their first project.)

                                     

                                     


                                    Randy wrote:

                                    in any relationship where 1-0 might ever happen, the foreign key should be put into the 0 side.  In a 1-1 where there is ALWAYS a record on both sides it doesn't matter which way you do it?  (I assume any design where 0-0 could ever happen is fatally flawed?)


                                    IMHO, in ANY 1:1 relationship you put the foreign key wherever it's more convenient. I am not sure what you mean by 0-0. You could certainly have a situation of 100 employees and 50 parking slots, where some employees are not assigned a parking slot, and some parking slots are not assigned to anyone.