[AccessD] Table for Emp, Location, etc

Virginia,

Also, can an Evaluator be an Employee or Supervisor?  If so, then a
person can play more than one role in these relationships and you have a
M-M relationship.

Charles Wortz 
Software Development Division 
Texas Education Agency 
1701 N. Congress Ave 
Austin, TX 78701-1494 
512-463-9493 
CWortz at tea.state.tx.us 
-----Original Message-----
From: John W. Colby [mailto:jcolby at ColbyConsulting.com] 
Sent: Thursday 2003 Apr 03 09:10
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Table for Emp, Location, etc


That depends on whether an object can have more than one Location,
Supervisor etc.  Your second solution allows as many combinations as you
need.  The first allows each to have one.

I would think you would have:

Employees
Evaluators
Locations
and Supervisors

Assuming:

Each Employee has one supervisor, then the Employee table has a Foreign
Key SupID
Each Employee has one location, then the Employee table has a FK LocID
Each Employee has one Evaluator, then the Employee table has a FK EvID

You haven't really expressed the relationships of all the objects.  Do
Evaluators have locations?  Do supervisors have locations?  Only one?
If only one then an ID field directly in the Evaluator table to relate
the Evaluator to the location.  If more than one, then a M-M table
relating the evaluator to many locations.
John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
Hollis,Virginia
Sent: Thursday, April 03, 2003 9:58 AM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Table for Emp, Location, etc


On setting up a tables for employees, evaluators, their locations and
supervisors. Is it better to create a table for each (location,
employee, supervisor, evaluator) then in each table have the locationID,
supervisorID, etc. Or is it better to have each table & join it together
in one table with only the key fields, EmpID, SupervisorID, LocationID,
EvaluatorID?

Virginia