Re: Table design teaser

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 15:23:00 -0400

Drop the distinction between staff and customer. Have a 'SENDER' table with
an enumerated status (customer, staff, BOSS,, etc) field. 

-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"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
    ... Why do STAFF and CUSTOMER have to be separate tables? ... > 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