Single centralized or seperated log table for modules ?

Question

Hi All

I am designing intranet system for medium business. Should i keep single log table for all modules or make it separate ? Audit log keeps all admin/staff activities(create, update, delete objects) and the log structure is universal for any kind of module. And also is it good idea if i pull report based on log records ? My log table keeps object type and object id so i could fetch data for any object and at any time based on event,object name and object id. Whats is the best approach for reporting in such cases?

Has anyone experienced with that ?

Thanks.

Answer 1

See log4php. log4j solved much of the logging problems by introducing log hierarchy and levels. I don't know how good log4php is, but it should be a starter.

Answer 2

I'd say one table.

You may want to find user activity across all modules, for example (if I understand correctly). Which lends itself to one table.

Reporting off your log table would be OK. You offload into a separate reports database to reduce contention and load on the logging table.

Finally, I would store object name and type explicitly (database objects). If you DROP and CREATE then the ID will change. Or a table may become a view for example so both it's type and objectid will change.

Answer 3

Well, when you go to review your logs, which would you rather do, look in one place where you can see everything or have to check several different places, each of which just shows one piece of the system in isolation?

Keep in mind that, with a single table, it's trivial to filter out entries which are irrelevant or which the user does not have authorization to view. Combining several individual logs into a single, comprehensive view is a bit trickier to do, plus it has the additional disadvantage of, under most designs, requiring you to revisit the code which does the combining each time a new log table is added.

I definitely say a single log is preferable. The only situation I can think of where multiple segregated logs would be appropriate would be if security concerns were strong enough to require that log entries with different visibility must be physically segregated - and, in such a case, you'd probably be looking at separate log servers, not just separate tables.

Answer 4

We use single table and it proved to be the best solution, particularly in performance. And especially with large data sets. If you're interested in off-the-shelf solution - try this.