SQL Server 2008 R2, SQL TABLE, When multiple users update the same field in the same row at the same time?
I have web based email system I have coded in ASP.NET 4.5 C# primarily coded using Javascript and Jquery with a SQL SERVER 2008 R2 backend.
The problem is we let each user of that system have their own read and unread email flags.
We first approached this problem by creating a tblMessageReadByUser table which contained a user record for each and every email that came in. Which you can guess can grow really fast.
Our second approach was to create a separate tblMessageState table, one record for every email. Which contains a string of 1024 1's and 0's and based on user id each user "byte" would be marked as 1 or 0. (we originally thought byte array field but there is a 32 limit and there could be more users than that.
But even this solution isn't 100 percent solid, there is funkiness when the different users read or unread the same email updating the same field at the same time.
Any thoughts or suggestions on the optimal way to handle this scenario? We are upgrading to SQL Server 2014 next year if helps.
The problem is we let each user of that system have their own read and unread email flags.
We first approached this problem by creating a tblMessageReadByUser table which contained a user record for each and every email that came in. Which you can guess can grow really fast.
Our second approach was to create a separate tblMessageState table, one record for every email. Which contains a string of 1024 1's and 0's and based on user id each user "byte" would be marked as 1 or 0. (we originally thought byte array field but there is a 32 limit and there could be more users than that.
But even this solution isn't 100 percent solid, there is funkiness when the different users read or unread the same email updating the same field at the same time.
Any thoughts or suggestions on the optimal way to handle this scenario? We are upgrading to SQL Server 2014 next year if helps.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>That's a thought, but know that the user has the ability to mark the email as read and unread at will.
in which case you can add back the row again, not a problem. still, at the end of the day, most messages will be marked as read.
>Any thoughts on how to manage the exponentially large growing tblMessageReadByUser table in terms of size and performance?
no other way that to have enough storage for the db. it's to note that the size of the information that you need to store who has read/not read a message is small compared to the messages themselves...
>We also do not do hard deletes in our system. We have IsActive =1 Is Delete =0 scenario once a record is created.
this is perfectly possible for the main entities, but for such "unimportant" links, this should not be done.
performance is the main issue here
in which case you can add back the row again, not a problem. still, at the end of the day, most messages will be marked as read.
>Any thoughts on how to manage the exponentially large growing tblMessageReadByUser table in terms of size and performance?
no other way that to have enough storage for the db. it's to note that the size of the information that you need to store who has read/not read a message is small compared to the messages themselves...
>We also do not do hard deletes in our system. We have IsActive =1 Is Delete =0 scenario once a record is created.
this is perfectly possible for the main entities, but for such "unimportant" links, this should not be done.
performance is the main issue here
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys I appreciate the input big time!
ASKER
If we stay the relational route, Any thoughts on how to manage the exponentially large growing tblMessageReadByUser table in terms of size and performance? Emails come in quite often. If either way...we create a tblMessageReadByUser table , say that creates a record everytime the user reads an email or vice versa, its still going to be a large table.
We also do not do hard deletes in our system. We have IsActive =1 Is Delete =0 scenario once a record is created. Or for this particular table should we abandon that for this particular tblMessageReadByUser table and scenario?