If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Database design problem

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,333
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.
Reply With Quote
  #3 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
of for that matter Receivers, aren't both the same entity as Users?
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #5 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
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
File Type: pdf himeda.pdf (75.4 KB, 8 views)
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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;
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On