General pointers/theory on handling people/contacts in design

[Cheese_whiz]

Hi all,

I don't have a firm grip on how to handle people within some of the
applications I do and could use a little feedback.

I'm redoing an application that tracks greivances, and there are several
different roles that people play in the process: greivant, manager, mediator,
witness, etc etc. The problem is it's possible for a person in one role in
one greivance to play another role in another greivance.

Generally speaking, I'd think that you would put all people in one table,
and then let the 'role' they are playing be specified in another table (say
the greivance table). But it seems like if you do that, then you can't
really sort them out to, for example, populate a combobox with just those
people who are of a certain class of people (mediators, for example, as
opposed to greivants or witnesses).

I'm just trying to get a better feeling for how to store data on people.
All in one table? Seperate tables for each role and then combine all the
tables in a query if you need access to all names at once....

Does any of this make sense?

CW

 

[KARL DEWEY]

Have at least three tables --
People
Functions OR Roles
PeopleRoles - this is a junction table and includes fields for StartDate,
EndDate, Remarks, etc.

 

[mscertified]

Each grievance will be an entity and have a number of people linked to it.
The same person can be linked more than once in different roles.
So link your grievance table to a table of person-roles. Roles and people
will be in their own tables and linked to person-roles. A problem you may
have is if the roles change during the course of a grievance.

-Dorian

 

[Jerry Whittle]

You have many-to-many relationships. Therfore you need at least 4 tables.
People, Roles, Grievances, and Joining.

The People table has all the information about the individuals. Make an
Autonumber field called PeopleID the primary key.

The Roles tables has information about the roles such as the role name,
responsibilities, etc. Make an autonumber field named RoleID the primary key.

The Grievances table has information about the specific grievance such as
GrievTitle, GrievDesc, GrievDate, etc. Also have a GrievanceID autonumber
field as the primary key.

The Joining table also have a JoiningID autonumber field as the primary key.
It also has PeopleID, RoleID, and GrievanceID fields which are the foriegn
key fields to link back to their respective tables. You create a from based
on the Joining table and have three combo boxes that use queries based on the
other tables as their Row Source. Now you can select a person from the People
table to be a certain Role in a particular Grievance.

For this to work the People, Role, and Grievance tables need to be populated
first. You may also have to institute certain business rules such as there
can only be one mediator per grievance.

Also you should define the relationships between the tables in the
Relationships Window and enforce referential integrity. I would not enable
Cascade Delete and if you use Autonumbers for primary keys, there's usually
no reason for Cascade Update.

 

[Cheese_whiz]

Thanks all for the replies. That helps a lot.

CW