mysql creating a user log

Member_2_8034623
Member_2_8034623 used Ask the Experts™
on
Hi

I want to create a detailed user log.

currently, I have created a table that stores the Id, date, userId, table name and table id.

if I run a query it works fine to display basic details. however, if I want to display the contents of the table name and Id I inserted I run into a crappy situation where I would need to create a procedure. the procedure would run the basic select and dynamically join the other table based on the table name and table id I inserted into the log database.

another problem is that not at tables are the same therefore this method would only partially work anyways.

the table names field is manually added when adding the log data set

how can I accomplish a nice log?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
What do you want to log.. The normal answer is everything but this just duplicates a lot of data that is already present somewhere else.
user logs in to application
user views
user crud data
user crud data
user crud data
user reports data
user disconnects.

Author

Commented:
Hi

I am concerned with user crud data

I realize even keeping a log is a duplication of some data points in the larger dataset held within the individual tables. However, to accomplish a simple select on the log table i would need to create multiple unions creating an ever-expanding select statement
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Do you want to record that the user changed something or do you want to record what the user actually changed?

How do you want to use the data - once you have data in your log - how do you envisage consuming the data - what sort of extracts will you want to run?
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

Commented:
Hi,

I want to give a user an accurate listing of creation and updates a user has performed within the service. I would display these via html with links to the corresponding dataset.

Eventually, i would want it to display the version levels on an update (similar to what WordPress does with their page versions)

The overarching mechanism is to have an audit trail of everything the user is doing.

I am not concerned with reading or deletion(The user doesn't actually delete anything in the database)
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Here is the solution I use - it might not be the best fit for everyone for reasons that will become apparent.

Before I write to a table I pass the new record to a method on the class that manages the table - the method does a diff on the new data compared to the old data and produces an object of the changes.
This is sent to a log file (json_encoded) along with the user ID, the operation they performed and the table they performed the operation on.

This information is used to show both an audit trail and as a means for admins to approve changes - the diff object is displayed to the admin who can then either approve all the changes or only certain fields (as required) - this too is logged.

The method of json_encoding the meta data you need is useful in that it allows you to keep a tight table structure while still being able to support any change.
The drawback is that you don't have select / join capability on the fields inside the JSON string. The idea would be to optimise the structure so that your table configuration is simplified but you are able to access the data you need from the table easily.

Author

Commented:
A json encoded string makes a wack of sense and allows for some serious flexibility. i didnt even think of that!

Thanks
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You are welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial