CODEWITHSUNDEEP
phpmysqlsql
Nick Shears
Nick Shears

Reputation: 1133

How to structure a user activity table in MySQL?

I'm currently having a single activity table that references other tables depending on the type of activity.

id | type | user_id | source_id | misc_id | date

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

This is working well for a user activity stream, but the only problem is, I can't figure out what to do about rows that no longer exist in the relative tables?

E.g. a user creates a status and then deletes it, or a user becomes friends with somebody that is later deleted from the database.

If the activity was relative to a single type, then I would be able to add a foreign key constraint, which would remove the row; but as it's relative to different tables, how else could I go about doing this?

Upvotes: 1

Views: 1567

Answers (2)

Bulat
Bulat

Reputation: 6969

Here are my thoughts.

If users can delete something, you can record this also in your activity table.

I you want to hide that activity, you can add ReversedOn field and update it with the relevant date. Then you will just have to filter out activities that don't exist.

If that does not cause any user experience problems, then you can just let it be.

Upvotes: 1

Slowcoder
Slowcoder

Reputation: 2120

You will have to take either of these approaches.

  1. When the user deletes, just do the soft delete on the backend by marking them as deleted instead of hard deleting from the table. You will have to introduce a new column "delete_flag" in this approach.

  2. Archive the tables and move the records to a different table when deleted. But this would be complex coding wise as well as the performance might not be as expected.

Upvotes: 1

Related Questions