Architecture, Logging user activity on rest backend server for handle custom user UI-CodePudding

I want to make logging module user activity on the rest backend server. Our web service's UI transform with user activity histories.

Example, There is Users and Projects.(User - Project(M:N))

User's projects list be sorted by recent clicked project. I can make something like below for versatility, // Make userAction table that has this scheme.

{ user_id: 1, targetDocument_id: 2, action: 'view', targetEntity: 'project', createdAt: '2022-06-29 08:22',...}

If the user requests their project list, I query the userAction table and manually sort. Or, I can make this function easily with add custom fields to the project table. But I think there is better practice for like this purpose. Isn't it? Any advice..?

Thank you.

CodePudding user response：

Is it fast enough? With indexes? Then keep what you have. Don't optimize if you don't need to.

If you find that this approach is unacceptably slow, you could denormalize the data and create a table like:

user_id
project_id
last_access_time

You probably already have a table with the first 2 fields, so adding a new field will make this very fast.

Do you need to denormalize? It depends on the size of the data and what scale you're expecting. If you have lots of users and generating tons of log entries I suspect that the denormalized approach quickly becomes desirable, but if you have 1 user that uses the application a few times per week you may never get there.

There is a cost to denormalizing. It's more code, and it duplicates data, so use it when you (think) there's a benefit.