Hello, I'm still a rookie to MS Access, using version 2003, and would like some help/input on determining table relationships for a few examples. I do have one other post at the MS forums in the 'new user' forum but I've become a little confused from the input there.
I've typed out my thoughts below on whether they should be one-many or many-many relationships and would like confirmation and/or other ideas to make this as simple as possible. I'm in the process of reading a book called 'Databases DeMystified' to assist me with understanding relationship logic.
1. I have a parent table called 'tblIssue' (with Primary key (autonumber) of 'IssueID'). I also have several other tables that I want to relate to the parent, which are listed below. These all have a Foreign key of 'IssueID' (number/long integer).
a. tblArea (table to log in what areas an issue(s) occurs). If I understand the logic correctly, a single record/ISSUE could occur in one or many AREAS at any give time & one or many areas could have one or many issues. So I would establish this as a MANY-MANY relationship using a junction table, correct? In the junction table I plan on having the two primary keys for the 'tblIssue' and 'tblArea' to make the connection between these two tables.
b. tblComment (table to log comments/updates for an issue). I see this as a ONE-MANY because one ISSUE can have MANY associated comments but all of these comments are only associated with the one ISSUE, correct?
c. tblIssueType (table to log and categorize the type of issue): Not sure here - I can see that an ISSUE is generally one TYPE, i.e. system-related, at any given time (but could change before being resolved) but an ISSUE can also be different TYPES at one time, correct, i.e. both system-related and user-error? So, I am thinking this would be a ONE-MANY relationship.
d. tblIssueStatus (table to log and categorize the current status of an issue): An ISSUE only has one STATUS, i.e. open, at any given time but that STATUS can change, i.e. from 'open' to 'resolved'. So, I am thinking this would be a ONE-MANY relationship, correct?
e. tblIssuePriority (table to log and categorize the current priority of an issue): An ISSUE can only have one PRIORITY at any given time, i.e. Urgent, but that STATUS can change, i.e. from 'urgent' to 'secondary'. So, I am thinking this would be a ONE-MANY relationship, correct?
My ultimate goal is to create one primary form via the parent table (tblISSUE) and then create subforms between the parent table and the child tables to ADD to the primary form,, which would populate the aforementioned tables, but I need to have the relationships well thought out first, of course.
If I am not understanding the relationship logic, please advise. Thank you kindly.
Start Free Trial
