Link to home
Create AccountLog in
Avatar of Member_2_8034623

asked on

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?
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

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.
Avatar of Member_2_8034623



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
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?

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)
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
A json encoded string makes a wack of sense and allows for some serious flexibility. i didnt even think of that!

You are welcome.