Icon / labeling of content that has changed since the last time a user viewed it

Question

I was wondering if there is a common database structure for the following situation.

There are users and projects in my web application.

Now, whenever a project is created or modified, all users who have not seen this new / modified project should still be notified in some way (for example, the icon in front of the project name or something else)

Is there a general method for doing this? If so, which method and how can I do this in the most efficient way?

Answer 1

, "" ", ", - . , ( , , , ) - (U, P) , " U P". , ( , , , , , , ).

SQL , ?

: SQL, ( InnoDb, mysql), , uid pid :

create table Seen (uid int, pid int, 
                   foreign key uid references user(uid) on delete cascade,
                   foreign key pid references project(pid) on delete cascade,
                   primary key (uid, pid))

pid P , "", delete from Seen where pid=P (.. P ); U, U , and uid!=U where delete.

, U P, insert ignore into Seen(uid, pid) values (U, P) ( ignore " , ", "" , , , ).

Answer 2

, , ( -) .

create table user_seen_project
(
user_id int, 
project_id int, 
last_seen datetime
);

.

create table project
(
    project_id int
    ...
    last_changed datetime
);

, ,

select project_id, /* other fields*/ last_changed, last_seen 
 from project p outer join user_seen_project up
 on p.project_id=up.project_id
 where up.user_id=$currentUser

:

• last_seen null - ( )
• last_seen < last_changed -
• last_seen > last_changed - ""

Answer 3

, .

Answer 4

, - "" (). - , - . , triggers .

, ( ) .

Answer 5

, -

users_viewed_projects
+--------+-----------+
+ userId | projectId |

userId/projectId .

Answer 6

. , , , , , .

, , , , .

, . , , , - , . kigurai, .