Table "Best Practices" question. Make a case for your preference

[Sunapee Chair]

What would be the best practice for the following scenario?

Tracking 100 different processes by the individuals authorized to perform
each. 100 individuals. Many individuals can perform any given process.
Individuals can perform multiple processes. Each process has a
three-character code and a longer explanatory name. Each person has an ID
code number and a full name. Search needed by both all processes for a given
person AND all people authorized for a given process.

Option A: Separate record for each person performing each process (very many
simple records, numerous records for each person)

Option B: Single record for each person with a field for every process
(fewer records, each record very long)

Option C: Single record for each person with a single field containing all
processes for that person (fewer records, one field in each that potentially
contains numerous data points)

 

[NetworkTrade]

a Processes Table
a AllPeople Table
a Activities Table that cross references the actual activities of the other
2 tables

 

[Beetle]

Option D: None of the above

You have a many-to-many relationship between Processes and People
so you need three tables.

A table to hold info about each Person

tblPeople
*******
PersonID (Primary Key)
FirstName
LastName
other fields that hold descriptive info about each person

A table to hold info about each Process

tblProcess
********
ProcessID (PK)
ProcessDescription
other fields related to each process

A third (junction) table to define the relationship

tblPersonProcesses
**************
PersonID (Foreign Key to tblPeople)
ProcessID (FK to tblProcess)
other fields that are related specifically to the relationship between
a Person and a Process (i.e. date assigned, date completed, etc.)

In this table the PersonID and ProcessID woul be a combined PK. Optionally,
you could add a separate PK field.

 

[Jeff Boyce]

Here's a 'case' for the two suggestions you've already received (use 3
tables to handle your many-to-many relationship) ...

You're posting in an MS Access newsgroup ... I assume you want to make best
use of MS Access.

The way to handle many-to-many relationships in MS Access is to use three
tables, as described.

Regards

Jeff Boyce
Microsoft Office/Access MVP