Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Question Unanswered: Possibly simple db design question

    I'm trying to design a DB to facilitate a fairly simple website system.

    Basically a user can login and ask a 1-to-1 question of an expert. The expert logs in and sees the message and replies. The original message and the replies appear downwards on the screen, almost exactly like a thread in this forum works, except it's a 1-to-1 conversation and not something anyone else can view or contribute to. A private messaging system of sorts.

    The expert can also, if they want to, write notes beside each question, but these are only viewable by the expert, the person asking won't see the expert's notes.



    So, my thoughts were, for the message part of the DB (the user tables I'm happy enough with, they're already created) is to have something like:

    MsgID
    SubmitterUserId
    MsgContent
    DateAdded

    I'll also have a Notes table that will have the MsgID and the NoteContent so that the note can appear beside the relevant message.

    But I don't know if the above is enough to create the system as I've described.

    Can anyone spot any huge flaws? I was thinking the messages would display by dateAdded but I've no idea how to keep note of which message relates to which, as in which is a reply to which and so on.

    Any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, the submitting user submits a message using that table design...

    ... but how does the application know which expert it's for?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Quote Originally Posted by r937
    okay, the submitting user submits a message using that table design...

    ... but how does the application know which expert it's for?


    Doh...damn, this is confusing me now. Had thought it was simple and that I just couldn't see the woods for the trees. Now am thinking it's more complex than originally i thought.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hint: add ExpertID to the table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    3
    ok, with some help I was thinking something like this:


    User Table

    UserID
    FirstName
    Surname
    etc...


    Topic Table

    TopicID
    Title
    DateCreated
    etc…


    Message Table

    MessageID
    ConversationID
    MessageText
    DateCreated
    etc…


    UserConversation Table

    UserID
    ConversationID


    Does this seem right?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the UserConversation table doesn't seem to do much about the earlier problem i mentioned

    one of the ways that you can "test out" a table design is to make up some rows of sample data using data that is as realilstic as possible

    don't just use IDs 1,2,3 all over the place, but try to use values that actually relate the tables together

    that way you can see whether your tables actually relate properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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