Dear Mus:
There are three types of relationships: one-to-one, one-to-many, and
many-to-many. This is how a many-to-many relationships is constructed.
This is common and proper (if they've done it right).
The "extra" table is a junction table showing which rows from each table are
related. I do not believe it can be implemented with just the two tables,
as you seem to think. If you invent a way to do that, please let us know!
Now, considering all managers are probably employees, I would set up a
relationship with the one side being EmployeeType: Regular, supervisor,
manager, executive. The other table of all employees wuld be the many side.
This one is usually pretty simple. But, if it is necessary to say someone
is both a regular employee and an executive, but not a supervisor or
manager, then it would need to be a many-to-many relationship. On the form
of employees, I'd create this by having a multi-select combo box of all the
EmployeeTypes, and have it highlight those that are chosen.
If you think you're going to put the 1 to 4 Employee types (listed above in
my example) all into the one employee table row, please consider how you
will do this and allow them to expand to 1000 EmployeeTypes, without
changing the Employee table. Or 1,000,000 Employee Types. The method we
use now, the junction table, allows for all this, without any change to its
initial design. Also, this method queries well. Junctions tables were
taken into consideration when query languages were developed.
It may be useful to think of the whole setup as a giant 2-dimensional
matrix. The rows and columns are labelled as employees and employee types.
The intersections are marked whether the employee is of that type or not,
that being determined by whether the junction table has a row for that
combination of employee and employee type or not. In addition, you can
store information about that employee being of that type, such as the year
he became a manager, or who promoted him. Someone could even be a manager,
get fired or quit, get re-hired, and be a manager again. Key the table on
the employee key, the employee type key, and the date hired or promoted.
The same person can then be hired or promoted to be a manager, then not be a
manager (maybe save the date of that in the junction table, too) then be a
manager again, over and over. So, having a place (the junction table) to
store information specifically about that employee at that type is valuable.
There's a lot more to making it work than just this, but that's a start.
The rest is generally VBA code behind the form, to make the list box appear
with the appropriate lines highlighted (Current event) and to save any
changes made (Before Update event).
Please let me know if this helped, and if there is more with which I can
assist you.
Tom Ellison
