mysql creating a user log


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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPRetiredCommented:
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.
Member_2_8034623Author Commented:

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
Julian HansenCommented:
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?
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

Member_2_8034623Author Commented:

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)
Julian HansenCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_8034623Author Commented:
A json encoded string makes a wack of sense and allows for some serious flexibility. i didnt even think of that!

Julian HansenCommented:
You are welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.