Understanding Relationship Logic of Multiple Tables

[yamefui]

Hello,

I am working on an issues database as a personal goal and I am attempting to
understand the logic of table relationship links. I've accomplished quite a
bit of reading on this via various sources such as this forum, Access Help
and the Complete Access Reference book (maybe too many!) and I think I've
confused myself:

1. I have 7 tables established.
2. The primary table is: 'IssueDescription' and it has a primary key of
'Issue ID'.
3. I have made 'Issue ID' the foreign key in all of the other 6 tables (each
table has its own primary key as well) and I, to ensure I have all tables
related, want to link the primary key to the foreign key in each table: a.
'IssueUpdate' (to document comments and updates), b. 'IssueUserContact' (to
document individual who reported issue), c. 'IssueArea' (to document the area
where issue is occurring), d. 'IssueStatus' (to document current status of
issue), e. 'IssuePriority' (to document priority level of issue), f.
'IssueType' (to classify the type of issue).

***My primary question now is how to understand the logic of the
relationship between the primary table and the other tables. For example, I
see a '1-many' relationship between the 'IssueDescription' table and the
'IssueUpdate' table because 1 issue can have many comments and updates. Am I
thinking this through correctly?

But what about the other 5 tables?

For example, if I link the primary table 'Issue Description' to the 'Issue
Type' table (which currently has 3 different issue types, 'Informational',
'Error', 'Project'), would that be a 1 to 1 relationship? I'm thinking that a
single 'Issue' can only be categorized as one of the above choices, correct?
Or should I be thinking that a single 'Issue' could possibly be one of three
different issue TYPES and therefore a 1 to many relationship?

Again, I'm just trying to understand the logic of how the tables should
relate via relationships. If you can provide some direction and advice on how
to link the other 5 tables, I would appreciate it greatly.

 

[Larry Daugherty]

Set theory on which relational databases are modeled requires that you
turn your thinking regarding relationships in a counter-intuitive
direction.

Assuming Autonumber Primary Keys in all tables, every *related /
child* table will have in its Foreign Key field the long integer value
of the Primary Key in the *parent* table. Note that the term Foreign
Key is used for convenience but it doesn't have to appear anywhere in
the documentation. Usually the Foreign Key field will have the same
name as the Primary Key in the Parent table. Instead of the parent
identifying each child record, each child record identifies its parent
record.

In the Relationships window you can run a Join line between the
ParentID field in the Parent table to the ParentID field in the Child
table and establish the Join type and Referential Integrity and enable
Cascading Deletes to assure that deleting the Parent record will
delete all related Child records.

Once the above is done, you will be unable to add a record to the
child table until the record in the parent table has been established.
The most common way of handling parent and child records is with the
Form/SubForm paradigm. There is ample Help on the subject. Once the
Form with its SubForm are in play the management of parent and child
records is intuitive and relatively painless.

HTH

 

[yamefui]

Hi Steve and Larry, thanks for the replies. Steve, I am somewhat confused
however. Regarding the additional tables you advise creating for:

'IssueArea'
'IssueStatus'
'IssuePriority'
'IssueType'

I am not sure how to create relationships for them or how exactly they
should be used/joined? These look like many to many relationships or I am
reading your intention incorrectly?

For example, with the two tables dedicated to AREA, would I relate them by
AREA ID and then relate them back to the 'IssueDescription' table by IssueID?

The original tables I set up for these four are intended for use as lookup
tables (since I do not want to use lookup fields in the tables themselves).
Each one already has several identifying adjectives.

If you can provide a little more direction on where you are going with your
suggestions, I would be grateful. Thank you.