Imagine a system where a user can make a whole lot of different actions, like:
- Making a purchase
- Liking something
- Opening a specific view (pretty much every click)
- Inviting a friend
- ...
The actions are stored in tables, example: purchase_log
, invite_log
etc. With "log", I mean the action history of our users. Not system logs.
The actions need to be acted upon, examples:
- Display user statistics (when opening profile view for example)
- Counting actions toward giving achievements (by a separate service, performed once a minute or so for all users)
My questions:
- Is it bad practice to have a separate table like
user_statistics
where a simple count is incremented, instead of counting/searching over thexxx_log
-tables? This obviously breaks the normalisation rules. - Does the answer change depending on system load / size of application?
- Does the answer change when considering transaction locks / concurrency issues?
- Does the answer change with SQL vs NoSQL?
- Does the answer change with sync vs async systems?