Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: Help with tables (newbie)

    Hi,

    first question here.

    I need to store chat messages to a database. More exactly, the information I need to store is the sender, the chat message, and the receivers of the chat message. So, a table might look like this:

    ChatMessageID (int, key)
    SenderUserID (int)
    ChatMessage (Varchar)
    Receivers - ???

    How would I store the receivers? They can be one or many. Is this a "valid" solution:

    MsgID (int)
    ReceiverUserID (int)

    This means there is no key in the receivers table. The MsgID will occur as many times in the table as there are receivers for a chat message. Is that "acceptable"? Does anyone have another solution?

    Thanks for help!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Almost exactly perfect apart from
    Quote Originally Posted by xkrja View Post
    This means there is no key in the receivers table.
    The primary key is the combination, or composite, of the two columns.
    Composite Primary Keys

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You must also set up a foreign key to relate the ChatMessageID columns.

  4. #4
    Join Date
    Mar 2010
    Posts
    5
    Thanks for your help!

    Now all I have to do is to set this up in sql management studio.

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    Pootle flump: Just something I thought of here:

    In the "Receiver" table, do I really need the "MsgID" column if I have a foreign key relationship with the "ChatMessageID" in the "ChatMessage" table? I mean, isn't just the "ChatMessageID" and "ReceiverUserID" enough to create a composite key in the "Receiver" table?

    EDIT: I guess the answer is "no" after som more thinking. And the reason is that I need to set a value in the "Receiver" column in the "ChatMessage" table and that value should be the "MsgID" in the "Receiver" table. A bit messy but I hope you understand :-)

    Thanks again!
    Last edited by xkrja; 03-30-10 at 06:20.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by xkrja View Post
    A bit messy but I hope you understand :-)
    Nope

    When you've finished, post your final design for comment.

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    Ok, thanks again!

    So, this is what it looks like:

    Table T_ChatMessage

    ChatMessageID (int, primary key)
    SenderUserID (int, foreign key)
    ChatMessage (Varchar)
    Receiver (int)

    Table T_Receiver

    MessageID (int, primary/composite key)
    ReceiverUserID (int primary/composite key, foreign key)
    ChatMessageID (int, foreign key)

    What I haven't discussed is that I have a "User" table also that contains all userIds as keys. So the SenderUserID and the ReceiverUserID will be foreign keys. Have I got it right?

    I really appreciate your help!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Minor edits:
    Code:
    Table T_Receiver
    
    MessageID (int, primary key, NOT NULL)
    ReceiverUserID (int, composite unique key, foreign key, NOT NULL)
    ChatMessageID (int, composite unique key, foreign key, NOT NULL)
    The MessageID is actually an optional column - some would say you always should have one, some would say you should never have one, others would say it depends.

    I haven't got a good link to hand however it would be good to read a book, or at least do some thorough research, on relational database design.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by xkrja View Post
    I have a "User" table also that contains all userIds as keys. So the SenderUserID and the ReceiverUserID will be foreign keys. Have I got it right?
    Yes you have

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •