2

I am trying to implement a private messaging system in rails and I need help with the design of the database. I have read around and I have come up with many valid alternatives, but I was wondering if anyone knew of such a schema that would ultimately be scalable. I appreciate any help or links.

EDIT: The database schema would basically have two things

1.subject

2.message

I was thinking of implementing two tables

Messages

subject_text

sender_id

reciever_id

has_many texts

Texts

content_text

I am in no way any good at database schema but I appreciate the help while I am learning

Benny Hill
  • 6,191
  • 4
  • 39
  • 59
Seth Portman
  • 21
  • 1
  • 4
  • 6
    Why don't you add in the question the alternatives you came up with? – ypercubeᵀᴹ Dec 03 '12 at 17:28
  • basically most of them were very complex to try to explain but the simpler ones are basically very similar to http://stackoverflow.com/questions/6984750/private-message-database-design I have also edited the question to update what the messaging system would include. – Seth Portman Dec 03 '12 at 17:33
  • Are you far enough along in thinking about your features to decide about your schema? Are any of these features expected for instance: messaging to more than one recipient. has message been read. date of message. confirmation that message has been read. Also what is 'has_many texts'? – AllInOne Dec 03 '12 at 17:42
  • 1
    While you are at it, [here's something interesting to read on](http://highscalability.com/blog/2010/11/16/facebooks-new-real-time-messaging-system-hbase-to-store-135.html) about Facebook's messaging system that stores 135 billion messages/month. – Ranhiru Jude Cooray Dec 03 '12 at 17:43
  • @AllInOne The messaging system would be very basic one recipient, date of message, and of course the message. The 'has_many texts' is how you describe a relationship between two tables in rails. I did however forget to add 'belongs_to messages' in the texts table. – Seth Portman Dec 03 '12 at 17:47
  • @RanhiruCooray Thanks for the link I will be sure to take a look at it – Seth Portman Dec 03 '12 at 17:47

1 Answers1

6

A very simple and straight-forward design would be..

User
----------------
userID
userName
...
...
...

Messages
---------------
messageID
title
body

User_Messages
----------------
senderID (userID of sender)
recieverID (userID of reciever)
messageID (messageID of relative message)

This design will simplify your searches too. You can add or remove columns according to your needs.

Bhrugesh Patel
  • 1,096
  • 5
  • 20
  • 38
  • thanks for the help, im a noob at rails so please forgive many questions but how would you connect this in the models? – Seth Portman Dec 03 '12 at 21:58
  • initially I was asking if you knew how to implement this schema in ruby on rails, but I noticed that the question is under databases. However your answer was just what I needed I was able to get it working this morning. thanks – Seth Portman Dec 04 '12 at 16:25
  • 1
    You need to add timestamp. Otherwise new messages will get lost in inbox – Yoker May 22 '15 at 20:06