Best way to determine if a user has viewed data.

Question

What is the best method to determine if a user has viewed a piece of data, ie like an update to a comment. The two solutions I have thought about are these....

1. Use a separate table that has a row for each user and the data id that is being viewed and inserting into when the item was last viewed.

2. Use the same table and add a row for every user when the item is changed and delete that row when the user actually views the data.

Both methods solve the problem but in solution 2 the maximum rows for the table would at worst equal that of solution 1, no one viewed anything, and at best has 0 rows, everything has been viewed. I know in solution 2 you have no way to determine when it was viewed.

Thoughts?

Edit: I was using an update to a comment as an example. In the actual application, new users wouldn't be expected to view or read old data. It would mean nothing to them for they just joined.

Answer 1

It's got to be option 1:

Table 1 (comment)

• comment_id
• comment

Table 2 (comment_view)

• comment_id
• user_id

Option 2 will not work because every new user will have every existing comment "marked read."

Answer 2

do you need to know when or how many times they viewed a piece of data?

If not I'd keep a table with a FK to the data-updated and a FK to the user. then a simple check to see if they viewed:

select count(*) from DataAlerts where dataid = 1 and userid = 1

when data is updated insert records for the data and users.

when a user views delete that user.

Answer 3

Option 1 is the better approach here - generally, deleting something to indicate that something has happened is a strange way to do things in a database, and as ng.mangine indicated above, it won't even work (unless you add rows to that table every time there's a new user, but THAT is almost certain to be a bottleneck and perf issue - consider what happens when you have a million comments and a new user joins).

Option 1 is much clearer; only think about changing it if you encounter performance issues (or if you happen to know in advance that your traffic will require a more optimized strategy, like bit vectors on each topic representing read / unread for each system user).

Answer 4

Option 2 is an unusual way of doing things, which in the database world is a way of saying it might work but it's probably best to go with a more traditional approach like Option 1.