Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to make a small messaging system. It should have these properties:

  • One user can send a message to one or many users
  • Messages can be sent only to registered users (i.e. to an existing 'idUser' record)
  • There should be tracking of sent messages (who sent the message and to whom)

Here is how the current database design looks like:

enter image description here

Table Private Message stores only the content of the message.

Table User Private Message is used to keep track of sent messages. It has columns "idUser from" and "idUser to" which indicate the user that sent the message and the user (can be more than one) that received the message.

I am having difficulties with choosing the right type of relationship (if the whole concept is correct in the first place) for the tables "User" and "User Private Message". I assume that there should be two separate relationships for the tables 'User' and 'User Private Message', like this:

  • One-to-one relationship, that connects User.idUser and User Private Message.idUser from
  • One-to-many relationship, that connects User.idUser and User Private Message.idUser to

Could you please advise me on what relationship to use here. If you have any suggestions on how to approach this type of problem easier, I would appreciate that even more.

Thank you all for your time.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Assuming you're not trying to keep track of threads of messages, I.E. you send a message to N people, and they can't reply:

1) user needs no references to other tables
2) a message should have an ID (obviously) and a reference to the user who sent it originally
3) you need a table that connects a single message and its recipients (users) that looks like this: message_recipients{user_id, message_id} where the primary key is the combination of the two fields. This ensures that one message can have many recipients, and that users can be the recipients of many different messages.

One user can send a message to one or many users

This is accomplished via the message_recipients table

Messages can be sent only to registered users (i.e. to an existing 'idUser' record)

You'll need to handle this in your application logic as well, but if you add (as you should) a foreign key and (depending on your DBMS) a NOT NULL restriction on each of the columns in message_recipients table, it will be possible to create a record in message_recipients without the recipient existing.

There should be tracking of sent messages (who sent the message and to whom)

This data model supports that. Example:

Find the name of all recipients of messages sent by user Mike Johnson

(edited to give better aliases to the two different uses of the user table)

SELECT recipient.firstname, recipient.lastname
FROM user recipient
JOIN message_recipient mr ON recipient.id = mr.user_id
JOIN message m ON m.id = mr.message_id
JOIN user sender ON m.user_id = sender.id
WHERE sender.firstname = 'Mike' and sender.lastname = 'Johnson'
share|improve this answer
    
Thank you very much Petter for the detailed answer. This is much better than my approach. –  Mike Johnson Feb 10 '13 at 17:24
    
You're welcome! I might not be an expert on DBA things, but TAing a university course on database fundamentals with a strong focus on taking a spec and turning it into a functioning database via an EER model and a relational model did at least leave me prepared for questions like this ;) –  Petter Brodin Feb 10 '13 at 17:33
    
Hehe, sure it did :-). Thanks –  Mike Johnson Feb 10 '13 at 17:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.