Whats a better strategy for storing log data in a database?

Question

Im building an application that requires extensive logging of actions of the users, payments, etc.

Am I better off with a monolithic logs table, and just log EVERYTHING into that.... or is it better to have separate log tables for each type of action Im logging (log_payment, log_logins, log_acc_changes)?

For example, currently Im logging user's interactions with a payment gateway. When they sign up for a trial, when trial becomes a subscription, when it gets rebilled, refunded, if there was a failure or not, etc.

I'd like to also start logging actions or events that dont interact with the payment gateway (renewal cancellations, bans, payment failures that were intercepted before the data is even sent to the gateway for verification, logins, etc).

EDIT: The data will be regularly examined to verify its integrity, since based on it, people will need to be paid, so accurate data is very critical. Read queries will be done by myself and 2 other admins, so 99% of the time, its going to be write/update.

I just figured having multiple tables, just creates more points of failure during the critical mysql transactions that deal with inserting and updating the payment data, etc.

Answer 1

It depends on the purpose of logging. For debugging and general monitoring purpose, a single log table with dynamic log level would be helpful so you can chronologically look at what the system is going through.

On the other hand, for audit trail purpose, there's nothing like having duplicate table for all tables with every CRUD action. This way, every information captured in the payment table or whatever would be captured in your audit table.

So, the answer is both.

Answer 2

All other things being equal, smaller disjoint tables can have a performance advantage, especially when they're write-heavy (as table related to logs are liable to be) -- most DB mechanisms are better tuned for mostly-read, rarely-written tables. In terms of writing (and updating any indices you may have to maintain), small disjoint tables are a clear win, especially if there's any concurrency (depending on what engine you're using for your tables, of course -- that's a pretty important consideration in mysql!-).

In terms of reading, it all depends on your pattern of queries -- what queries will you need, and how often. In certain cases for a usage pattern such as you mention there might be some performance advantage in duplicating certain information -- e.g. if you often need an up-to-the-instant running total of a user's credits or debits, as well as detailed auditable logs of how the running total came to be, keeping a (logically redundant) table of running totals by users may be warranted (as well as the nicely-separated "log tables" about the various sources of credits and debits).

Answer 3

Transactional tables should never change, not be editable, and can serve as log files for that type of information. Design your "billing" tables to have timestamps, and that will be sufficient.

However, where data records are editable, you need to track who-changed-what-when. To do that, you have a couple of choices.

--

For a given table, you can have a table_history table that has a near-identical structure, with NULLable fields, and a two-part primary key (the primary key of the original table + a sequence). If for every insert or update operation, you write a record to this table, you have a complete log of everything that happened to table.

The advantage of this method is you get to keep the same column types for all logged data, plus it is more efficient to query.

--

Alternatively, you can have a single log table that has fields like "table", "key", "date", "who", and a related table that stores the changed fields and values.

The advantage of this method is that you get to write one logging routine and use it everywhere.

--

I suggest you evaluate the number of tables, performance needs, change volume, and then pick one and go with it.