Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2012
    Posts
    7

    Database design problem

    Dear all;
    I have one problem while working for one application...Need your advice please.

    I have one table called USERS; which stores information about all users of some staff including their Username and Password and there is a chat application system need to be integrated in to my existing table USERS. Here the rule is
    - a user can send a message in to another single user,
    - a user can send a message in to many users (broadcasting),
    - a user can also send a message in to one's self.

    Now I want to prepare a table to store for all the messages interchanged among all the users. and i prepare my table design as follows based on the rules of database design concepts;

    USERS= (USER_ID->PK, FIRST_NAME, LAST_NAME,...USER_NAME, USER_PASSWORD)
    SENDERS= (SENDER_ID->PK, USER_ID->FK)
    RECEIVER= (RECEIVER_ID-PK, USER_ID->FK)
    MESSAGE= (MESSAGE_ID->PK, MESSAGE_CONTENT, DATE, SENDER_ID, RECEIVER_ID)

    Now; Is there any other easy way of handling my data... because i am having a problem while displaying the chat history of all users such as in to datagridview.

    Best regards;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,452
    Your design does not seem to support broadcast messages, does it? Also, what's the purpose of SENDERS?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,532
    of for that matter Receivers, aren't both the same entity as Users?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2012
    Posts
    7
    The need for tables SENDERS and RECEIVERS is that because I need to display the following information in my chat history using datagridview i.e.

    (SENDER_FULL_NAME, RECEIVER_FULL_NAME, MESSAGE_CONTENT, DATE).

    So if I should display the above data in my message history; I need to answer questions like who is sender? and who is receiver?

    What other designs do u recommend me...let me see that please?

    Thank you for your attention to my problem;

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,532
    fine
    but that doesn't mean you need separate tables for Sender and receiver, assuming that a sender and receiver have to be members. there is nothing stopping you having a FK from MESSAGE TO USERS for SENDER AND having a FK from MESSAGE TO USERS for Receiver.
    the two tables are superfluous, when the data is already in USERS

    granted if a sender and or receiver isn't already a user then you need to resolve that. not knowing your application I dunno, but it feels as if a sender and receiver should be a user.

    as n_i suggests there is no way you can have multiple receivers for the same message, again as we don't know your application they may or may not be a consideration

    get your data design right before worrying about the detail of the presentation / user interface
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2012
    Posts
    7
    Dear healdem;
    As i already tried to mention above...
    - Both the senders and receivers are Users and
    - The application supports broadcasting message i.e. a single message from a single user can be sent to multiple users (receivers). Having this in your mind....

    If i make a direct relation between the tables USERS and MESSAGES; I will have the following kind of relations.


    USERS= (USER_ID->PK, FIRST_NAME, LAST_NAME,...USER_NAME, USER_PASSWORD)
    MESSAGE= (MESSAGE_ID->PK, MESSAGE_CONTENT, DATE, SENDERS, RECEIVERS, USER_ID->FK)


    And from the above relations how I can identify who is the sender and who is the receiver?
    Note that according to the database design rule we cannot repeat the records for sender and receiver information i.e. we need to use a foreign key to refer for an existing records
    My question here is; using a single foreign key attribute (USER_ID) in the MESSAGES table
    How can I refer to two different users (if the sender and receivers are different users?)

    Thank you for help;

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,532
    You have a FK for the Sender in messages to users
    You have a FK for the receiver in messages to users
    You doont have userid in messages (well you do but as sender and receiver)
    You can pull the names of the sender and receiver in messages when using a query which joins to the users table twice (once for sender, once for user)
    If you need to model a multi receiver message you'd probably want to model a many to many relationship and implement that as an intersection table. Where you'd add another table which is name up of the message id and receiver (user id) as the pk. There can be multiple userid (receivers for a single message) a user my also have multiple messages.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2012
    Posts
    7
    Hello healdem;
    If i truly get your point; you are telling me that I should put
    some FK for the senders and receivers in addition to the USER_ID (FK)
    which migrates from table USERS to table MESSAGES.

    What kind of FK do you think i can have for the senders and receivers other than
    a single USER_ID?
    I thought if i may put the usernames of the senders and receivers in the message table
    but my data (username) will be redundant.

    I just politely request you to share me the tables design that you are thinking!

    Best regards;

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,532
    you already had the basic design in your first post, except
    delete the SENDERS and RECEIVERS tables
    the SENDER_ID and RECEIVER_ID are instead foreign keys to USERS.USERS_ID

    the _1 tables reflect a way of having multiple receivers for a specific message.
    you'd need an intersection table to allow a one to many relationship between MESSAGES and RECEIVER. My guess is that you'd probably want to implementing the _1 version, unless you have a one SENDER and one RECEIVER.
    You dont' need the USERNAME in USERS, you can always derive the username from the firstname and secondname. However if username is meant to represetn something else (eg a user 'handle' such as himeda.2000) then by all means keep it.


    oops just noticed there should be a FK from MESSAGES_1.SENDERID to USERS_1.ID
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2012
    Posts
    7
    Dear healdem;
    Thank you very much for your help...i really appreciate the attention you paid in solving my problem. Hope; i will see you in some other title of discussions.

    Best regards;

Posting Permissions

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