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:

  1. Is it bad practice to have a separate table like user_statistics where a simple count is incremented, instead of counting/searching over the xxx_log-tables? This obviously breaks the normalisation rules.
  2. Does the answer change depending on system load / size of application?
  3. Does the answer change when considering transaction locks / concurrency issues?
  4. Does the answer change with SQL vs NoSQL?
  5. Does the answer change with sync vs async systems?
  • Don't denormalize unless you have to. A properly designed table should deliver excellent response. – paparazzo Jun 29 '16 at 13:04

I think the answer will vary depending upon what you want to achieve.

As you have mentioned that these things need to be acted on analysed, reported & displayed then I would consider that having the data in tables would be advantageous.

The decision to calculate summary data vs calc on the fly depends upoon size volume and scalability and accuracy required. A mixture of real time and batch processing might be viable and practical approach.

I would expect performance would be an important consideration. Do end users need to see their stats updated immediately? Or are you collecting this data to drive marketing analysis which can happen periodically at a later date.

Go back to your requirements. What data do you need. Who needs it. When do they need it. How fast & how often. Do they need raw data or aggregated. Detail or Summary? How much data are you expecting to collect. How long do you need to retain it? What are the storage requirements and how will that perform?

If you can answer the questions above you may narrow down your choices and your remaining options will be clearer.

| improve this answer | |

I think (from my experience) that logs should be treated as separate part of the system and stored in files, or at least de-normalized.

You'll probably want to log that sale failed, when the database is down... and you'll probably want to have access to your logs even if something wrong happens with database... for async systems... data is not guaranteed to be written, so if you're using async datastore that's another reason to log to files.

Probably you're talking about some kind of "history" rather than "log" (so you'll be writing events that happen, not too much technical data). Like if you sold a TV... price, time, user, shipping details, status... that's probably history.

I'd create normalized "history" tables for such events, with foregin keys on user-id, and for logging i'd use text files.

| improve this answer | |
  • Yes, I mean a form of history - not system logging. It's just what I like to call the tables, because it makes sense in our app. – Mattis Jun 29 '16 at 14:50
  • 1
    Personally, I'd include the logs in the database - you can then join the log tables with non log ones if required - try doing that with text files! Also, if the database is down, who's going to be looking at reports from the logs anyway? Also, if your logs are in text files, how are you going to be able to query them at all, never mind join them against other tables? How do you get a COUNT(missed_sales) from a text file? Use a MySQL .csv engine? To @Mattis - if you've just started this project and haven't picked a database and want a F/LOSS one, go for PostgreSQL, - vastly superior! – Vérace Jun 29 '16 at 15:07
  • @Vérace I am using Postgres, it's very sweet - but I'm missing some MongoDB-stuff :) – Mattis Jun 29 '16 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.