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

You would have something like follows:

Activity | Description | Recipient Type

Where Recipient Type is one of Contact or Customer

You would then execute a SQL select statement as follows:
Select * from table where Recipient_Type = 'Contact';

I realize there needs to be more information.

We will need an additional table that is representative of Recipients(Contacts and Customers):

This table should look as follows:

ID | Name| Recipient Type

Recipient Type will be a key reference to the table initially mentioned earlier in this post. Of course there will need to be work done to handle cascades across these tables, mostly on updates and deletes. So to quickly recap:

Recipients.Recipient_Type is a FK to Table.Recipient_Type

Answer 2

The "right" design for this database is to have one column for each, which you say you are trying to avoid. This allows for a proper foreign key relationship to be defined between those two columns and their respective tables. Using the same column for a key that refers to two different tables will make queries ugly and you can't enforce referential integrity.

Activities table should have foreign keys ContactID, CustomerID

To show activities for employee:

SELECT ActivityName, ActivityDescription, CASE WHEN a.ContactID IS NOT NULL THEN cn.ContactName ELSE cu.CustomerName END AS Recipient
FROM activity a
LEFT JOIN contacts cn ON a.ContactID=cn.ContactID
LEFT JOIN customers cu ON a.CustomerID=cu.CustomerID

Answer 3

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

Answer 4

    [ActivityRecipientRecipientType]
    ActivityId
    RecipientId
    RecipientTypeCode
        |||   |||  |||_____________________________    
         |     |                                  |
         |     --------------------               |
         |                        |               |
    [Activity]                [Recipient]      [RecipientType]
    ActivityId                RecipientId      RecipientTypeCode
    ActivityDescription       RecipientName    RecipeintTypeName


    select 
      [Activity].ActivityDescription  
    , [Recipient].RecipientName
    from
      [Activity] 
  join [ActivityRecipientRecipientType] on [Activity].ActivityId = [ActivityRecipientRecipientType].ActivityId
  join [Recipient] on [ActivityRecipientRecipientType].RecipientId = [Recipient].RecipientId
  join [RecipientType] on [ActivityRecipientRecipientType].RecipientTypeCode = [RecipientType].RecipientTypeCode
  where [RecipientType].RecipientTypeName = 'Contact'

Answer 5

Here is my stab at it:

Basically you need activities to be associated to 1 (contact or Customer) and 1 employee that is to be a responsible person for the activity. Note you can handle referential constraint in a model like this.

Also note I added a businessEntity table that connects all People and places. (sometimes useful but not necessary). The reason for putting the businessEntity table is you could simple reference the ResponsiblePerson and the Recipient on the activity to the businessEntity and now you can have activities preformed and received by any and all people or places.

Answer 6

It's not clear to me why you are defining Customers and Contacts as separate entities, when they seem to be versions of the same entity. It seems to me that Customers are Contacts with additional information. If at all possible, I'd create one table of Contacts and then mark the ones that are Customers either with a field in that table, or by adding their ids to a table Customers that has the extended singleton customer information in it.

If you can't do that (because this is being built on top of an existing system the design of which is fixed) then you have several choices. None of the choices are good because they can't really work around the original flaw, which is storing Customers and Contacts separately.

1. Use two columns, one NULL, to allow referential integrity to work.

2. Build an intermediate table ActivityContacts with its own PK and two columns, one NULL, to point to the Customer or Contact. This allows you to build a "clean" Activity system, but pushes the ugliness into that intermediate table. (It does provide a possible benefit, which is that it allows you to limit the target of activities to people added to the intermediate table, if that's an advantage to you).

3. Carry the original design flaw into the Activities system and (I'm biting my tongue here) have parallel ContactActivity and CustomerActivity tables. To find all of an employee's assigned tasks, UNION those two tables together into one in a VIEW. This allows you to maintain referential integrity, does not require NULL columns, and provides you with a source from which to get your reports.

Answer 7

Actions
Activity_ID | Description | Recipient ID
-------------------------------------
11    | Don't ask questions | 0
12    | Be cool    | 1

Activities
ID | Description
----------------
11  | Shoot
12  | Ask out

People
ID | Type | email | phone | GPS |....
-------------------------------------
0  | Troll | [email protected] | 232323 | null | ...
1  | hottie | [email protected] | 2341241 | null | ...


select at.description,a.description, p.* from Activities at, Actions a, People p
where a."Recipient ID" = p.ID 
  and at.ID=a.activity_id

result:

Shoot | Don't ask questions | 0 | Troll | [email protected] | 232323 | null | ...  
Ask out | Be cool | 1 | hottie | [email protected] | 2341241 |null | ...

Answer 8

Answer 9

If I've read the case right, Recipients is a generalization of Customers and Contacts.
The gen-spec design pattern is well understood.

http://stackoverflow.com/questions/3879806/data-modeling-question/3880673#3880673

Answer 10

Model another Entity: ActivityRecipient, which will be inherited by ActivityRecipientContact and ActivityRecipientCustomer, which will hold the proper Customer/Contact ID.

The corresponding tables will be:

Table: Activities(...., RecipientID)

Table: ActivityRecipients(RecipientID, RecipientType)

Table: ActivityRecipientContacts(RecipientID, ContactId, ...,ExtraContactInfo...)

Table: ActivityRecipientCustomers(RecipentID, CustomerId, ...,ExtraCustomerInfo...)

This way you can also have different other columns for each recipient type

Answer 11

I would revise that definition of Customer and Contact. A customer can be either an person or a business, right? In Brazil, there's the terms 'pessoa jurídica' and 'pessoa física' - which in a direct (and mindless) translation become 'legal person' (business) and 'physical person' (individual). A better translation was suggested by Google: 'legal entity' and 'individual'.

So, we get an person table and have an 'LegalEntity' and 'Individual' tables (if there's enough attributes to justify it - here there's plenty). And the receiver become an FK to Person table.

And where has gone the contacts? They become an table that links to person. Since a contact is a person that is contact of another person (example: my wife is my registered contact to some companies I'm customer). People can have contacts.

Note: I used the word 'Person' but you can call it 'Customer' to name that base table.