We help IT Professionals succeed at work.

mysql creating a user log

Member_2_8034623
on
97 Views
Last Modified: 2018-09-20
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

David Johnson, CDSimple Geek from the '70s
CERTIFIED EXPERT
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

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?

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)
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Thanks
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
You are welcome.