Database design schmea

Last Post 16 Mar 2006 06:21 AM by tmatrat. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dstoltz
New Member
New Member

--
21 Feb 2006 08:03 AM
Hi,

I am trying to design a database, that is somewhat of a cross between a web forum, and email. It's web based, and the users are able to send messages back and forth...

I need the messages to somehow be flagged if they've been read.

Most importantly, this part confuses me: Let's say you have USER A, and USER B...

"USER A" send MESSAGE1 to "USER B". "USER B" clicks on it to read it (changing the flag to read), and then replies. Let's say both users reply several times to this message.

How do I design the tables, such that, each reply shows up as a new unread item? And that previous replies don't show the full thread? In other words, previous, or earlier replies should only show the thread that was current up until then.

Does anyone have a few table layouts they would share that would accomplish this?

Thanks for any direction.
SQLUSA
New Member
New Member

--
02 Mar 2006 03:37 PM
You should keep the message body in a dedicated MessageBody table.

Message table should have the

FromUserID
ToUserID
MessageBodyID
DateSent
Subject
Read
Answered
PreviousMessage
.....

If Subject to long, you can place it into a dedicated table.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/orderdoubleheader/
The Best SQL Server 2005 Training in the World



dstoltz
New Member
New Member

--
03 Mar 2006 03:09 AM
What about if USER A can send a message to multiple people? That table wouldn't work...
amansbains
New Member
New Member

--
13 Mar 2006 12:42 AM
Why don't you change the field ToUserID to ToUserIDs. Storage can be done just like in Email Clients i.e. keep more space for ToUserIDs and store ToUserIDs seperated by commas or semicolons and parse it at runtime.
dstoltz
New Member
New Member

--
13 Mar 2006 02:16 AM
If you do that, then it would be impossible to determine if USER A has read the message yet.
tmatrat
New Member
New Member

--
16 Mar 2006 06:21 AM
Hi dsholtz,

Perhaps should you proceed like that :

- one table to manage Inbox/SendTo
(
idMsg integer,
FromUser
ToUser
Read bit,
idOriginalMsg integer
)

- one table for message content


Then the complete procedure should be divided in 3 steps:

STEP 1 : Write an email (to multiple people)

INSERT INTO ... VALUES('1', iduser1, iduser2,0,0)
INSERT INTO ... VALUES('1', iduser1, iduser3,0,0)
INSERT INTO ... VALUES('1', iduser1, iduser4,0,0)

STEP 2 : user3 read its email

UPDATE ... SET Read=1
WHERE idMsg=1 AND toUser=idUser3

STEP 3 : user3 answers to user1's email

INSERT INTO ... VALUES('2', iduser3, iduser1,0,1)

It should be better normalized, as idOriginalMsg and idUser1 are redundant in STEP3

Hope this will help you




You are not authorized to post a reply.

Acceptable Use Policy