1 person found this helpful
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?
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??
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 ?
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).
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 ?
I replied to the OP, not you dude
Oh , sorry for the friendly-fire
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?
Yeah, that's not right!
You should have a table (Called something like UserMessages) which has three columns.
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)
= 825000 bits
= ~100k which isn't at all bad for 25k users.
Plus you'll get massively more performance doing it this way!
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!
Couldn't have explained it better myself.