Re: Table design teaser

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 17:49:09 -0400

Why do STAFF and CUSTOMER have to be separate tables? Have one user table,
and if you need to distinguish, have a column that tells the type (staff,
customer). If there are other attributes you haven't mentioned, they can be
in a child table with an FK reference to userid... now you don't have to
wonder whether id 101 = staff or id 101 = customer.

ID, by the way, is probably the worst column name ever... and probably is
part of the reason for your confusion. 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Keith Harris" <anonymous@discussions.microsoft.com> wrote in message
news:EBE8A8D3-8AAF-434A-961C-2198482B6F72@microsoft.com...
> 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

  • Re: Table design teaser
    ... Drop the distinction between staff and customer. ... > CUSTOMER (ID int pk, ... STAFF_CUSTOMER_MESSAGES (FromID int, ToID int) ...
    (microsoft.public.sqlserver.server)
  • Re: PADI Training vs.. others
    ... the customer decides to go on a scheduled boat ... He communicates this intent to the staff by signing his name ... which is where his dive gear is stored. ... BC & regulators on one of the AL80's in the tank. ...
    (rec.scuba)
  • Re: BIG BUG in Deleting detali records
    ... in the database in order to enforce referential integrity. ... when the customer himself is deleted from the master table. ... customerID int identity not null primary key, ...
    (borland.public.delphi.database.ado)
  • Re: *O/T*..overheard in a currys digital (FKNA Dixons)..
    ... Middleaged, well dressed guy asks a member of staff " Excuse me, I ... Customer " I don't mind spending a fair bit. ... Currys Staff " Well, sir the best thing you should do is go next ... door to WH Smiths, have a quick read of the camera magazines, they ...
    (uk.media.tv.misc)
  • Re: TSWLTH revisited
    ... eager to tell us that there WERE knowledgeable and helpful staff. ... that member of staff seems to have vanished, Her situation is one I have ... customer, a helpful and knowledgeable fellow customer can ensure a good ... I would never tolerate being treated badly in a store where I ...
    (alt.sewing)
Quantcast