Issue Database Design

[j2curtis64]

I've created a database that I am using to track issues. I have two tables.

Contact table that consist of the following columns:

ID, full Name, Position, Site, HierLevelSix, and CC

Issues table consists of the following columns:

Monthly, CH_ID, AO_ID, DateIssueOpened, LastUpdateDate, RequisitionNumber, TransactionNumber, Category, Status

I am using a form to insert records in the Issues table.

To not have duplicate information in both tables, I am using the CH_ID and AO_ID fields in the issues table. These ID's correspond to the values in the contacts table (ID).


Here is a record from the contacts table

ID LastName Position Site HierLevelSix CC CREDIT LIMIT Card Status
161 STEVE STEPHENSON CH DN 12345 888888 $1.00 A
158 MARSHA BRADY AO DN 12345 121212 $2.00 A


Here is a record from the Issue table

ID MonthlyPackage CH_ID AO_ID DateIssueOpened LastUpdateDate RequisitionNumber TransNumber Category Status
19 01-Dec-10 161 158 24-Mar-11 03208324 1 Justification INFO Closed


So how would I link the two tables to get good information for a report? For each issue opened there are two people assigned (Position-CH, AO).

I would like to show in a report Month, LastName, Position, Site, CC, DateIssueOpened, LastUpdateDate, RequisitionNumber, TransactionNumber, Category, and Status

 

[jdraw]

I'd suggest a junction table - ContactIssue.

You have a Contact table and an Issue table which can be a Many to many relationship. You need to relate them such that an Issue can Involve 1 or more Contacts.

ContactIssue table has a compound unique index (to prevent duplicates) made up of

Contact ID (from Contact table)+ Issue Id (from Issue table)


Here's an excerpt from M$oft

Many-To-Many Relationships
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s primary key).