SQL Server Performance Forum – Threads Archive
Message system
Hello! I want to build a message system on a website. The users should be able to send messages to each other. All users shall have a inbox/outbox/sent My question is now how I should build the tables with performance on my mind. About 55000 messages per day are going to be sent on my system. Either I could store two messageges. One message that the user who sent the message has stored in his "sent"-box. And a copy of that message but with data for the user who got the message. Example of database post…boxes (TABLE)
id name
1 inbox
2 outbox
3 sent messages (TABLE)
UserID box message
1 3 THE MESSAGE
2 1 THE MESSAGE Or I can almost the same.. but move out the message to not have that redundance.. messagesData (TABLE) id message
1 THE MESSAGE messages (TABLE)
UserID box messageID
1 3 1
2 1 1 The other thing I can think of is storing the message on one row, like this:
FromUserID ToUserID fromUserBox toUserBox messageID fromUserDelete ToUserDelete
1 2 3 1 1 1 0 The example above is rather ok but it will require more complex indexes as I see it. Since there is about 55000 new messages per day It will quick fill up the table. And it will be 2 million messages pretty quick and then it stays there… (members delete old messages and members are removed). Does anyone have an experience with a message system with alot of messages?
Can you share some thoughts? If you give some ideas please also give me information about how you think about indexing this. Thanks!
I would have 2 tables. One for current activity…one to roll messages n days older too… Don’t "move" any data. Keep status columns…maybe an additional table with userids and message ids with to and from userids…so the message is still only stored once… You;ll probably need a parent child thread type of id’s in the message table moo Brett
]]>