Table design teaser

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Keith Harris (anonymous_at_discussions.microsoft.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 12:16:13 -0700

Hi and thanks in advance for any help.

I am developing a system for message delivery and pondering what is the best table design.

Here's the situation:
A staff member may send a message to a customer or another staff member
A customer may send a message to a staff member or another customer

I have the following tables:

STAFF (ID int pk, Name varchar(50))
CUSTOMER (ID int pk, Name varchar(50))
MESSAGE (ID int pk, Body varchar(200), FromID int, ToID int)

Here are some sample rows from the tables:

**********************************************************************************
STAFF
-----
101, keith
102, kenneth
103, Steven

CUSTOMER
--------
101, charles
102, annette
103, tom

MESSAGE
-------
101, message from kenneth to keith, 102, 101
102, message from kenneth to charles, 102, 101
103, message from charles to keith, 101, 101
104, message from annette to keith, 102, 101

**********************************************************************************

The problem is that MESSAGE.FromID and MESSAGE.ToID can relate to either STAFF.ID or CUSTOMER.ID.

i.e. Does message 104 go to keith or charles? Is message 104 from kenneth or annette?

I could create multiple link tables:

**********************************************************************************
1. STAFF_CUSTOMER_MESSAGES (FromID int, ToID int)
2. STAFF_STAFF_MESSAGES (FromID int, ToID int)
3. CUSTOMER_STAFF_MESSAGES (FromID int, ToID int)
4. CUSTOMER_CUSTOMER_MESSAGES (FromID int, ToID int)

But this seems inelegant.

**********************************************************************************
I could also design a MESSAGE_LINK table like this:

MESSAGE_LINK (Message_ID int, FromID int, FromTable varchar(10), ToID int, ToTable varchar(10))

giving this sample data:

**********************************************************************************
MESSAGE_LINK
------------
101, 102, 'STAFF', 101, 'STAFF'
102, 102, 'STAFF', 101, 'CUSTOMER'
103, 101, 'CUSTOMER', 101, 'STAFF'
104, 102, 'CUSTOMER', 101, 'STAFF'

and this doesn't seem desireable either. Does anyone have a suggestion as to the best way to design this type of relationship?

Relevant Pages