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.

 

[Steve]

See below .......

Steve

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).

Youve done it correctly! Consider however using a naming convention for all
your database objects. I suggest beginning the name of all tables with Tbl
to distinguish between queries which I suggest begin with Qry. Also avoid
soaces in database object names.

***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?

YES, correct!

But what about the other 5 tables?

The same principle above applies to all other tables.
Re: 'IssueUserContact'
You should have a table of employees with EmplyeeID as the PK. The
fields in this table would be descriptive fields about the employee such as
name, department, etc.
TblIssueUserContact
IssueUserContactID
Issue ID
EmployeeID
DateIssueReported

Re: 'IssueArea'
Assuming an issue could involve more than one area, you need another table
TblArea
TblArea
AreaID
Area

then ...............

TblIssueArea
IssueAreaID
Issue ID
AreaID

Re: IssueStatus
Assuming you want to keep a history of an issue's status, you need a status
table:
TblStatus
StatusID
Status

then ....... IssuePriority'

TblIssueStatus
IssueStatusID
Issue ID
StatusAsOfDate
StatusID

Re: IssuePriority
Assuming you want to keep a history of an issue's priority, you need a
priority table:
TblPriority
PriorityID
Priority

then .......

TblIssuePriority
IssuePriorityID
Issue ID
PriorityAsOfDate
PriorityID

Re: IssueType
Assuming an issue can be multiple typesy, you need a type table:
TblType
TypeID
Type

then .......

TblIssueType
IssueTypeID
Issue ID
TypeID

Steve

 

[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.

 

[Steve]

Regarding Issue Area, I suggested:
TblArea
AreaID
Area

then ...............

TblIssueArea
IssueAreaID
Issue ID
AreaID

My suggested relationships are:
AreaID in TblArea ===> AreaID in TblIssueArea
Issue ID in your table IssueDescription ===> Issue ID in TblIssueArea

Issue ID in TblIssueArea identifies a specific issue from your table
IssueDescription in each record in TblIssueArea. The same issue may be
represented in multiple records in TblIssueArea if an issue involves
multiple areas.

TblArea records a list of areas that ANY issue may possibly involve. AreaID
in TblIssueArea identifies a specific area from TblArea in each record in
TblIssueArea. The same issue may be represented in multiple records in
TblIssueArea if an issue involves multiple areas.

For example, say you have in TblArea:
1 AreaA
2 AreaB
3 AreaC

and issuedescription 1 involves AreaB and AreaC. TblIssueArea would look
like:
1 1 2
2 1 3

Steve