Database design for a private message system
-
So im designing a pm system for a game written in Ruby/On Rails and not quite sure how i would design the database side of the game to facilitate the desired features. The general message system looks like Table: users int userid ~ extra Table: messages int messageid int authorid longtext message datetime timestamp Table: message_to int key int messageid int recieverid What I'm looking to add is essentially custom folders. Meaning a user could create a "Diplomatic" folder and have the message appear under that folder. How would one go about facilitating the association between a message and a folder?
-
So im designing a pm system for a game written in Ruby/On Rails and not quite sure how i would design the database side of the game to facilitate the desired features. The general message system looks like Table: users int userid ~ extra Table: messages int messageid int authorid longtext message datetime timestamp Table: message_to int key int messageid int recieverid What I'm looking to add is essentially custom folders. Meaning a user could create a "Diplomatic" folder and have the message appear under that folder. How would one go about facilitating the association between a message and a folder?
You need to create a table for folders i.e. Table: folders int folderid text name and then in meesages table add int folderid. Then when you access a message you know which folder it belongs to.
-
You need to create a table for folders i.e. Table: folders int folderid text name and then in meesages table add int folderid. Then when you access a message you know which folder it belongs to.
Yes but that doesn't hand ownership of folders would one do Table: Folders int folderid varchar foldername int userid the do something like SELECT foldername FROM table WHERE userid="x";? then look into messages with SELECT message, folderid FROM table WHERE receiverid="x"; then output based on folder?
-
Yes but that doesn't hand ownership of folders would one do Table: Folders int folderid varchar foldername int userid the do something like SELECT foldername FROM table WHERE userid="x";? then look into messages with SELECT message, folderid FROM table WHERE receiverid="x"; then output based on folder?
Sorry, didn't get the part about folder ownership. This how I would set it up Tables: folders: id int name varchar userid int messages: id int fromid int toid int message varchar(250) messagedate datetime folderid int users: id int name varchar Then when you want to look up all messages from one person (say his id is 1) to another person (id = 2) in a particular folder (say id = 1) you would have a query like this
SELECT
m.message
FROM
messages m
INNER JOIN
folders f
ON
m.folderid = f.id
WHERE
m.fromid = 1
AND m.toid = 2
AND f.id = 1;