CODEWITHSUNDEEP
sqldatabase-designnormalization
Nick Hodges
Nick Hodges

Reputation: 17138

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

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?

Upvotes: 6

Views: 339

Answers (11)

Fabricio Araujo
Fabricio Araujo

Reputation: 3820

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.

Upvotes: 0

Andy
Andy

Reputation: 3743

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

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18950

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

Data modeling question

Upvotes: 2

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

alt text

Upvotes: 1

Rondo
Rondo

Reputation: 3721

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 | ...

Upvotes: 0

John Hartsock
John Hartsock

Reputation: 86902

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.

alt text

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 51008

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.

Upvotes: 4

Jamie Treworgy
Jamie Treworgy

Reputation: 24344

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

Upvotes: 4

amelvin
amelvin

Reputation: 9061

    [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'

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

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

alt text

Upvotes: 9

Woot4Moo
Woot4Moo

Reputation: 24336

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

Upvotes: 1

Related Questions