Results 1 to 11 of 11
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Referential integrity problem with related tables

    I have a question concerning the best way to handle a table relationship. My Contact_info table is the main table for entering demographic information about businesses. I want to add a field (Canvasser_ID) to the table that refers to an individual that will reach out to the business for donations. The individuals that do the reaching out are in the Canvass_list. My problem is that I want to add the business info now and come back later and add the Id of the individual that will be reaching out to the business but setting the relationships up the way I have in the image below with referential integrity enforced causes an error since the canvasser_Id field in the contact_list field is empty. If I don't enforce referential integrity it works fine but I am suspect that doing so sets me up for problems later and is poor database design. So what can I do so that I can fill in the canvasser_ID field in the contact_list table at a later time?? Should I default all new business entries to a fictitious canvasser_ID and fix it later? I would appreciate any comments or ideas.

    Thanks

    Click image for larger version. 

Name:	relationships.PNG 
Views:	26 
Size:	24.6 KB 
ID:	31594

  2. #2
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I think I solved my problem by setting the default value for canvasser_ID in the contact_list table to Null. Any problems setting the contact_list table up that way?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Referential integrity should not force a value to be in the Canvasser_ID foreign key field, it should just prevent entering a value that is not already available from Canvass_List. Should not have to set default value to Null.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Referential integrity should not force a value to be in the Canvasser_ID foreign key field
    Correct, but if you try to add a value to this field later and the value doesn't exist on the 1 side, you will generate an error. Perhaps the value OP tried to enter doesn't exist on the 1 side. That would be a good reason not to have a free-form text box for entering data in this field if that's the case (in lieu of a combo box).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Why do you need the table Canvass_list at all? It would have a meaning, when same person can be in several different canvass lists, but the table Canvass_list doesn't have any field like Canvass_ID, so this isn't possible.
    Simply change the Canvasser_ID in Contact_info to IsCanvasser (TRUE/FALSE type field), and ditch the Canvass_list table.

    P.s. Consider splitting the table Contact_info to tables Persons, Person_contacts, Person_Addresses and Person_Businesses.
    Persons: Person_ID, Salutation, First_Name, Last_Name, Contact_Name, Display_Name, Spouse_ID (assumes the spouse is also registered in Persons table - otherwise you must have fields for spouse's first and last names instead), IsCanvasser, Person_Status (instead of inactive - can have several integer values, of them one will mark Inactive status);
    Person_Contactas: Contact_ID, Person_ID, Contact_Type (phone, e-mail, etc.), Contact_Group (home, work, etc.), Contact_Text, Contact_Status;
    Person_Addresses: AddressID, Person_ID, Address_Group (home, work, etc.), Country, State, County, City, Address1, Address2, ZIP_Code, Address_Status;
    Person_Businesses: Business_ID, Person_ID, Business_Name, Business_Type, Business_Status.
    Last edited by ArviLaanemets; 12-11-2017 at 07:12 AM.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why do you need the table Canvass_list at all?
    Agree if the canvassers are users or staff and there is an employee table. Then that person's id from that table could be used as the canvasser. Otherwise, it seems like a table of customers to me, and we all know that's normal. If that's the case, I don't see an issue with the current design, as the canvassers could be freelancers or supplier staff. In truth I haven't really tried to digest all your suggestions because I'm not sure who the canvassers are.

    Not sure we've satisfied the issue of RI yet either.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Micron View Post
    Agree if the canvassers are users or staff and there is an employee table.
    OP has all canvassers from contact_info table, and from his set-up doesn't follow existence of any other source.

    Code:
    Not sure we've satisfied the issue of RI yet either.
    When the table Canvass_List is dropped, the issue will be moot.

  8. #8
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Here are some answers to the questions you have raised.

    I'm not sure who the canvassers are.
    This Db is for a non-profit. The canvasser will be board members, they will not be the same individuals as those that are contacts, that is why they are in a separate table.

    Correct, but if you try to add a value to this field later and the value doesn't exist on the 1 side, you will generate an error. Perhaps the value OP tried to enter doesn't exist on the 1 side.
    I tried to test that issue and made sure the contacts list table had a canvasser_ID that was also in the canvassers table but I still got the error. Once I put a default value of NULL in the tablet design for canvasser_ID, I could no longer recreate the issue.

    That would be a good reason not to have a free-form text box for entering data in this field if that's the case (in lieu of a combo box).
    Board members will be selected to canvass a group of contacts via a combo box on a form that details all the contact's data. The combo box of canvassers s limited to the list.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    OK. It looks like I misread the Canvass_List. Reread more carefully your start post, and now I have a question - for what is used the referential integrity between Canvass_List and Contact_Info?

    Btw. defining relations and referential integrity is not compulsory. I myself prefer to use VBA scripts to get same results at form level. And you always can use it for some tables, and not use it for other tables. In your case you can p.e. execute a delete or an update query on table Contact_Info in main form's OnOpen event to delete contacts with orphan Canvass_ID, or clear orphan Canvass_ID's, and do same in AfterDelConfirm event of form where canvassers list is managed.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    for what is used the referential integrity between Canvass_List and Contact_Info?
    IMHO, cascade deletes would be about it. Even then, not likely you'd remove a related record in Contact info just because you might (and I stress might) delete a canvasser. When you think about it, there's probably not much point for this relationship. Defining relationships does help when in query design view even though you are correct in that they are not required. They might be needed for cascade updates or deletes in some cases.

  11. #11
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Ok, thanks for all the comments. Since the DB is working well now I can move onto more pressing issues. I'm always trying to learn more about Db setup and I appreciate all your insights.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Referential Integrity problem?
    By doobybug in forum Access
    Replies: 13
    Last Post: 05-22-2017, 07:59 PM
  2. Problem with Relationship Referential Integrity
    By Radtastic10 in forum Access
    Replies: 3
    Last Post: 03-22-2016, 11:03 AM
  3. Referential Integrity On Linked Tables
    By ribena1980 in forum Import/Export Data
    Replies: 4
    Last Post: 08-25-2015, 11:56 AM
  4. Replies: 2
    Last Post: 04-18-2013, 05:56 AM
  5. Referential Integrity
    By Paul H in forum Database Design
    Replies: 3
    Last Post: 11-14-2011, 03:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums