php - How to structure a user activity table in MySQL? -

php - How to structure a user activity table in MySQL? -

i'm having single activity table references other tables depending on type of activity.

id | type | user_id | source_id | misc_id | date

the type column tells me kind of activity user has performed (follow, liked, befriend, status etc.) , source id contains table id relative type of action.

this working user activity stream, problem is, can't figure out rows no longer exist in relative tables?

e.g. user creates status , deletes it, or user becomes friends later deleted database.

if activity relative single type, able add together foreign key constraint, remove row; it's relative different tables, how else go doing this?

you have take either of these approaches.

when user deletes, soft delete on backend marking them deleted instead of hard deleting table. have introduce new column "delete_flag" in approach.

archive tables , move records different table when deleted. complex coding wise performance might not expected.

php mysql sql