Database design to log user actions in WordPress

Question

I need a table to log certain actions users make in WordPress.

As of now, this is the database schema I have camp up with:

id bigint(20) NOT NULL AUTO_INCREMENT,
uid bigint(20) NOT NULL,
type VARCHAR(256) NOT NULL,
data1 TEXT NOT NULL,
data2 TEXT NOT NULL,
data3 TEXT NOT NULL,
timestamp bigint(20) NOT NULL,
UNIQUE KEY id (id)

Let me clarify:

uid: User ID of the wordpress user
type: Type of action the user made (can be 'comment', 'new_post', 'login', etc)
data1/2/3: additional data (for example, ID of comment or post made)

To display the logs, I would query the database and run through a certain filter to get the text to display for that particular log. So it works something like this:

if( $type == 'comment') {
    $comment = get_comment( $data1 );
    $user = get_user($uid);
    echo "User {$user->name} has made a <a href='{$comment->permalink}'>comment</a>";
}

Is this the most efficient way of doing things? It seems quite fine to me as I do not want to just store HTML in the logs table to be outputted.

However, the problem comes where I want to hide a particular log entry when certain conditions are met. Like, for example, if a comment no longer exists, I want to hide that entry. This would pose some problems with pagination. Any suggestions on how I can overcome this?

Thanks!

EDIT:

myplugin_transactions
id bigint(20) NOT NULL AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
type VARCHAR(256) NOT NULL,
timestamp bigint(20) NOT NULL,
UNIQUE KEY id (id)

myplugin_meta
id bigint(20) NOT NULL AUTO_INCREMENT,
txn_id bigint(20) NOT NULL,
key VARCHAR(256) NOT NULL,
data TEXT NOT NULL,
UNIQUE KEY id (id)

Lets say I want to select * from myplugin_transactions where data1 would usually have had been 'x' and data2 been 'y'. How should I do it in this case?

SELECT * FROM myplugin_transactions LEFT JOIN myplugin_meta ON myplugin_transactions.id = myplugin_meta.txn_id WHERE ( ... ? )

Your data types could be better. Do you really need BIGINT for user_id? Do you expect to have more than 4 billion users? The timestamp column should probably be the TIMESTAMP type. Try to avoid the TEXT type; it will force any sorting to be done on disk. Unless you really need more than 255 characters, use VARCHAR(255). The type column on the main table should probably be VARCHAR(255) since making it 256 adds an extra byte. These changes can significantly reduce the size of the data and indexes. — G-Nugget
– G-Nugget, Commented Dec 5, 2012 at 18:55
@G-Nugget Thanks for your input about the difference in performance between VARCHAR(255) as opposed to VARCHAR(256). Would there be any performance improvements if I use the TIMESTAMP type? Also I chose BIGINT instead of INT as it is consistant with the WordPress database schema (codex.wordpress.org/Database_Description/3.3#Table:_wp_users). They probably used BIGINT for scalability. I'm a plugin developer and this is written for the general public in mind. Should I then use INT or stick with BIGINT? — mushroom
– mushroom, Commented Dec 5, 2012 at 18:59
I guess it's fine to use BIGINT if you're being consistent. What will the timestamp value actually be? Will it be a unix timestamp or a formatted date? Also, will it always be when the row is inserted to the table or will it be some other time? You should also make you INT type fields UNSIGNED; using BIGINT, you probably won't notice any difference, but it's the right thing to do. — G-Nugget
– G-Nugget, Commented Dec 5, 2012 at 19:04
@G-Nugget It will be a unix timestamp. I sometimes want to get entries that are say, one hour ago and would SELECT entries with timestamp greater than <?php time() - 3600; ?>. Also, I read this (stackoverflow.com/a/409305/1365792). In my case, shouldn't datetime be more suitable than timestamp? I know nothing about the implications of making my INT SIGNED or UNSIGNED. — mushroom
– mushroom, Commented Dec 5, 2012 at 19:09
Making an INT type UNSIGNED makes it so it can only have positive values, effectively doubling the max value. TIMESTAMP would probably be the best type. If you really need to manipulate the time with timezones, you can convert it. TIMESTAMP is smaller and basically is a unix timestamp to the server. TIMESTAMP also has the benefit of being able to use the current time as the default value, allowing you to simplify the queries by omitting the timestamp and having the server use the current time. — G-Nugget
– G-Nugget, Commented Dec 5, 2012 at 19:16

Christian · Accepted Answer · 2012-12-05 18:54:12Z

1

This answer is going to be very generic as it doesn't provide any code, but it's also too long for a comment.

Firstly, you shouldn't be storing additional data in those data1, data2, data3 fields. You're using MySQL, so you've got the power of relational databases. Use them.

You should simply have another table, which has an ID field (the ID of the action), and a data field. That way you can store 0 to as-many-items-as-you-want pieces of metadata. I mean, wordpress already does this with metadata right?

Secondly, if a comment is deleted, do you simply want to delete the action related to it? If so, simply hook into the API. I believe there is a hook for delete_comment: http://codex.wordpress.org/Plugin_API/Action_Reference#Comment.2C_Ping.2C_and_Trackback_Actions

Otherwise if you want to keep the action, you can either add an extra field or piece of metadata called, say, deleted. When a comment is deleted, as above: hook into the delete_comment call and update the action to deleted = true. Then when you run your query on all the actions, exclude the deleted statements, eg ... WHERE deleted = NULL ... etc.

EDIT2:

To answer your select statement, something like this could work:

SELECT * FROM myplugin_transactions 
LEFT JOIN myplugin_meta AS data1 
ON ( myplugin_transactions.id = data1.txn_id AND data1.key = 'data1' )
LEFT JOIN myplugin_meta AS data2 
ON ( myplugin_transactions.id = data2.txn_id AND data2.key = 'data2' )
WHERE data1.data = 'x'
AND data2.data = 'y'

Obviously replacing the data1 and data2 keywords with meaningful descriptions.

edited Dec 5, 2012 at 18:54

answered Dec 5, 2012 at 18:15

Christian

19.8k3 gold badges57 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mushroom Over a year ago

Thanks for your thoughts about using a secondary table for indefinately many addtional datas. As for the second part, I do not want to delete the log entry but rather just hide it from the user's view when certain conditions are met, but they mess with pagination as I may end up with 9 entries on the page of 10 if one gets filtered out.

mushroom Over a year ago

Thanks, that is a very good suggestion. I have edited my question as to how to query for something based on the suggested schema.

mushroom Over a year ago

What if I want to get ALL metas into the query? Do I have to do a lot of subquery? Or lets say to display the logs table, do I need to do a foreach loop for the main table, and depending on the type, query the meta table separately?

Christian Over a year ago

You only need to join the tables if you want to filter by a meta key using SQL. If you want all the meta data, do a separate query for all the metadata (SELECT * FROM myplugin_meta WHERE txn_id = {$id}). Right now I think you're combining a lot of questions at once, you might want to take a look at existing plugins to see how they work, try and build yours, then come back with specific questions.

mushroom Over a year ago

let us continue this discussion in chat

Collectives™ on Stack Overflow

Database design to log user actions in WordPress

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related