relationship display

[bkelly]

Access 2010, windows 7, 64 bit

Table tbl_Issues has a relationship with table tbl_Contacts. Each row in Contacts can be referenced by many rows in tbl_Issues. (Any person can open multiple issues.) I see that as a one to many relationship.

If an issue is deleted, there is no reason to delete any contact that was related to that issue. If a contact is deleted, there is no need to delete any issue that related to that contact. Yet it is a one to many relationship.

When I do not set the option Enforce Referential Integrity then the Access relationship view shows the relationship line without the 1 and the infinity symbols. But the Edit Relationship form continues to show One-To-Many.

What is Access trying to tell me that I don’t understand? Is this just an unimportant aspect of the relationship display?

 

[SydneyGeek]

If an issue can also relate to many contacts then you need a join table to resolve the relationship.

ie:
tbl_Contacts
ContactID (PK)
Other Contact fields

tbl_Issues
IssueID (PK)
Other Issue fields

tbl_ContactsIssues
ContactIssueID (PK)
ContactID
IssueID
Other relevant fields

Both Contacts and Issues need to be related one to many to the join table, with referential integrity enforced. Removing an issue from a contact just deletes the join record; neither Contacts or Issues are affected.

Denis

 

[xenou]

When I do not set the option Enforce Referential Integrity then the Access relationship view shows the relationship line without the 1 and the infinity symbols. But the Edit Relationship form continues to show One-To-Many

I could be wrong but I think Access will try to determine from the indexes on the tables whether there is a one-one or one-to-many relationship. Whether it is enforced with referential integrity is up to you. The difference in the way the lines appear is useful as it is a visual clue.

You can end up with "orphaned records" if you delete the records in one table that are referred to from another. Referential Integrity would disallow such deletions. Some DB admins will prefer to flag a record as "inactive" rather than delete it. I do that myself most of the time.

Sounds like a many-to-many to me as well, so a linking table is called for as noted by Denis (contacts related to many issues, issues related many contacts). Once these are defined in the DB, it's amazing how everything in the queries will just "fall into place" when you add the two tables, together with their linking table, to your query grid.

HTH,
ξ

 

[bkelly]

I will proceed along those lines. Slowly, but proceeding. Thank you.

 

[HiTechCoach]

Access 2010, windows 7, 64 bit

Table tbl_Issues has a relationship with table tbl_Contacts. Each row in Contacts can be referenced by many rows in tbl_Issues. (Any person can open multiple issues.) I see that as a one to many relationship.

If an issue is deleted, there is no reason to delete any contact that was related to that issue. If a contact is deleted, there is no need to delete any issue that related to that contact. Yet it is a one to many relationship.

When I do not set the option Enforce Referential Integrity then the Access relationship view shows the relationship line without the 1 and the infinity symbols. But the Edit Relationship form continues to show One-To-Many.

What is Access trying to tell me that I don’t understand? Is this just an unimportant aspect of the relationship display?

The point of referential integrity is to avoid orphaned records. Which is what you are doing.

Why would you ever need to delete a record if it has been used?

If you do not need a issue for some reason I find it better to use a status field that is set to sonme like "voided". You can then filter these repcrds out when not needed.

In general I never delete any work that has been done. From my Accouinting background deleting work is never done. You enter a reversing entery. You should always maintain an Audit trail.

Also never allowing to delete records make it a lot easier to recover from data entry mistakes with no lose in data.


What is Access trying to tell me that I don’t understand?

With RI turned on:
If you really want to delete a contact that has been linked to an issue then you will first have to "unlink" the contact from the issue. Either assign "link" the isue to another contact or to no contact ( "unlinked" issue). Once the contact record has no "links" to any other record in the database then you will be able to delete the record.

Note:
It is possible to use RI and not have a foreign key set. Basically is is an orphaned record. For example you my want to enter an issue but link it to a contact at a later date/time.