11 Replies Latest reply on May 31, 2010 10:34 AM by UbuntuPenguin

    Messaging system design pattern

    archemedia Level 4

      Hi,

       

      I'm working on a messaging system build into my application.

       

      As a system administrator, I create messages for all the users. When a user logs in, I want unread messages to be displayed so I have to keep track of wich user read or didn't read newly created messages.

       

      The simplest way would be to keep track of which message was read by which user but that would increment my database until the end of time (or at least until 2012 )

       

      I would think that it's better to keep track of which user didn't read which message but then things would get complicated when a message is deleted.

       

      Also, should I store the user with the message or should I store the messages with the user?

       

      Does anyone have experience on this? Any suggestions?

       

      It's not a technical problem but an architectural one.

       

      Thanks,

       

      Dany

        • 1. Re: Messaging system design pattern
          Gregor.Kiddie Level 2

          You're right, tracking which user hasn't seen which message is the better way to do it.

           

          Also, don't physically delete your messages. Do a logical delete instead. You still probably want user's to see messages which are out of date (unless they are of the Server down, server back up variety), so you should still retrieve deleted messages for user's who haven't yet seen them.

           

          In reality, you shouldn't ever do a physical delete on a piece of data. How can you ever have a proper audit if information is gone?

          1 person found this helpful
          • 2. Re: Messaging system design pattern
            archemedia Level 4

            Thx for the answer Gregor.

             

            You mention an interesting topic: '...In reality, you shouldn't ever do a physical delete on a piece of data...'

            Doesn't this approach leave you with enormous amounts of redundant data??

             

            Dany

            • 3. Re: Messaging system design pattern
              UbuntuPenguin Level 4

              I would have a users table, then a messages table with a foreign key to the receiving user , the messages table would have a column for READ_STATUS. I'm pretty sure the database can handle storing 5-10 columns.  Are the users able to mark a message as "unread" if so , how does your model of only keeping unread messages handle that.  First the message was read , now it's "unread" and somehow has to be pushed back into the db.  If you index both columns by their keys , you can increase read performance and you will be fine.

                I assumed the messages weren't extremely time dependent , more like login messages as opposed to financial trading messages.  Oh , do you have an ORM layer ?

               

              Sincerely ,

                Ubu

              • 4. Re: Messaging system design pattern
                Gregor.Kiddie Level 2

                That suggests you are creating large amounts of redundant data in the first place, which just smacks of inefficiency.

                 

                The major reason for never deleting a piece of data is to cover yourself legally. In fact many industries require certain levels of data retention.

                 

                In your example, if you were sued by one of your customers relating to a message which was displayed several months ago, would you prefer to be able to produce the offending message and prove they are in the wrong, or potentially lose the action as you cannot prove your position.

                 

                As another example, if you are auditing data access by user, allowing users to be created used and deleted orphans all the audit data (and masks who it was that actually did the actions).

                • 5. Re: Messaging system design pattern
                  UbuntuPenguin Level 4

                  In the example all messages are kept , nothing is deleted ?  That is what the READ_STATUS column is for.  I don't see the data redundancies either ?

                  • 6. Re: Messaging system design pattern
                    Gregor.Kiddie Level 2

                    I replied to the OP, not you dude

                    • 7. Re: Messaging system design pattern
                      UbuntuPenguin Level 4

                      Oh , sorry for the friendly-fire

                       

                      • 8. Re: Messaging system design pattern
                        archemedia Level 4

                        ok, suppose I have a READ_STATUS field, how would you store it then?? You have to keep track of ALL your users and that for all your messages. Suppose you have 25000 users? What I do now is store a comma delimited string for all unread users. So, in a new message, I create a string with the id's of all current users, like 1,2,3,4....24999,25000. In Flex, I convert this string to an array, which makes it easy to delete the current user's id and then write this string back to the db. Something inside me tells me that there have to be more effecient ways to do this...?

                         

                        BTW, I don't use ORM, that's just the problem. If I have a person with several addresses, I keep a seperate table for the addresses and retreive those before sending a single person value object back to Flex. As it's not likely that a person would have more than 100 addresses (unless he is a criminal or Michael Jackson) this method is fine, but with storing thousands of user id's per message, well... suggestions?

                         

                        Dany

                        • 9. Re: Messaging system design pattern
                          Gregor.Kiddie Level 2

                          Ouch!

                           

                          Yeah, that's not right!

                           

                          You should have a table (Called something like UserMessages) which has three columns.

                          UserId

                          MessageId

                          ReadStatus

                           

                          Each user will have multiple entries in here, one for each message, and it will record whether the message has been read for each user.

                          If you've got 25000 users, it sounds like a lot, to add 25000 rows to the table for each message, but all you are really adding is...

                           

                          33 bits (32 for the int, 1 for the Boolean)

                          X 25000

                          = 825000 bits

                          = ~100k which isn't at all bad for 25k users.

                           

                          Plus you'll get massively more performance doing it this way!

                          • 10. Re: Messaging system design pattern
                            archemedia Level 4

                            Yes, that's the 'traditional' way to do it, using a link table. I just thought there were other ways of which I wasn't aware...

                             

                            Thx for the advice!

                             

                            Dany

                            • 11. Re: Messaging system design pattern
                              UbuntuPenguin Level 4

                              Couldn't have explained it better myself.