Linking multiple tables together

I have an application that I am working on and am trying to decide the best
way to accomplish this.

I have a table of phone numbers (DIDs) that we own. I also have an employee
database that these numbers can be assigned to.

Pretty straight forward so far... I just have another table that relates the
two together so an employee can have as many numbers assigned to them as I
wanted.

The problem: Some of these numbers might not always be assigned to an
employee. They can also be assigned to other items like call campaigns,
computers, fire alarms, etc... How do I allow these same phone numbers to be
related to other tables as well?

One idea was to have the link table contain multiple keys from the different
tables...

phone_id
employee_id
process_id
computer_id

Allow all but the phone_id be null and make unique based on all of them
combined...

Another would be to create a new column in each table called assignee_id
that is unique across all tables. This one value would be used in the link
table:

phone_id
assignee_id

Problem is, how would I know what table the assignee_id came from? I guess
it may not matter depending on how I access it...


Is there a better way to do this?

Thanks

Karl Albrecht

Karl

How important is it that you keep additional info about your "assignees"?
If you'll need to keep differing kinds of data about persons/employees vs.
processes vs. computers vs. fire alarms vs. ..., you'll need tables specific
to those. In that case, you could create a "master" table that held
AssigneeID and AssigneeType, related to each of those separate other tables
(a 1:1 relationship).

If you DON'T need more than simple common info about every assignee, no
matter what type, lose the Employee table (specific) and use Assignee
(generic).

JOPO (Just one person's opinion).

Jeff Boyce
Access MVP