What is the best way to design this particular database/SQL issue?

Question

Here's a tricky normalization/SQL/Database Design question that has been puzzling us. I hope I can state it correctly.

You have a set of activities. They are things that need to be done -- a glorified TODO list. Any given activity can be assigned to an employee.

Every activity also has an enitity for whom the activity is to be performed. Those activities are either a Contact (person) or a Customer (business). Each activity will then have either a Contact or a Customer for whom the activity will be done. For instance, the activity might be "Send a thank you card to Spacely Sprockets (a customer)" or "Send marketing literature to Tony Almeida (a Contact)".

From that structure, we then need to be able to query to find all the activities a given employee has to do, listing them in a single relation that would be something like this in it simplest form:

-----------------------------------------------------
| Activity | Description    | Recipient of Activity |
-----------------------------------------------------

The idea here is to avoid having two columns for Contact and Customer with one of them null.

I hope I've described this correctly, as this isn't as obvious as it might seem at first glance.

So the question is: What is the "right" design for the database and how would you query it to get the information asked for?

Answer 1

It sounds like a basic many-to-many relationship and I'd model it as such.