One table with flags or multiple tables ?

[David]

I have a need for a 'People' table. There are
various 'roles' for people like Manager, Project Manager,
Engineer. A person can fulfill multiple roles. There are
also columns that apply to all roles
like 'active/inactive'.

My first idea was to have a single 'People' table then a
series of yes/no flags for each role. But then I thought
that's not really normalized and maybe I should have
multiple tables, one for managers, one for engineers etc.
But then I run into the problem of the same person being
named differetly in different tables.

I suppose I could have a 'People' table with just a name
and id and then have multiple other tables referring to
those ids.

Any thoughts?

 

[John Vinson]

My first idea was to have a single 'People' table then a
series of yes/no flags for each role. But then I thought
that's not really normalized and maybe I should have
multiple tables, one for managers, one for engineers etc.
But then I run into the problem of the same person being
named differetly in different tables.

You're quite right: neither approach is correct.

You have a classic Many (people) to Many (roles) relationship. The
proper table structure would be to have THREE tables: People; Roles
(with a RoleID and an editable name for that role, and perhaps other
fields such as a memo field for a verbose description of what it takes
to be a manager); and a third table RoleAssignments with the PersonID,
RoleID, and perhaps some other fields (maybe a date range or a
comments field).

 

[David]

An excellent idea, I should have thought of that.

However I'm thinking this approach may place an
administrative burden on the system with regards to
maintaining these tables. Currently this system uses an
approach where there is one master table with most of the
data in it. There is a column called, lets say 'manager'.
The drop-down is based on a query with a SELECT DISTINCT
off this column. The combo boxes are marked 'not
restricted to list', so its up to the user to add new
values. The downside is we have had cases where a user was
lazy and did not check the list and added a duplicate name
spelled slightly differently.
Is there an easy way to make the more structured approach
of using 3 linked tables self-updating.

-----Original Message-----

 

[John Vinson]

Is there an easy way to make the more structured approach
of using 3 linked tables self-updating.

Yes; use a Form based on the People table, with a Subform based on the
RoleAssignment table to do all your data entry. You can use a combo
box on the Subform to select the role, and put code in its NotInList
event to make it possible to add a new role, but sufficiently
difficult (with "Are You Really Sure!?" propmts and so on) that users
won't do so casually.