title: Database design to log user actions in WordPress
body: <p>I need a table to log certain actions users make in WordPress.</p>
  
  <p>As of now, this is the database schema I have camp up with:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Let me clarify:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>if( $type == 'comment') {
      $comment = get_comment( $data1 );
      $user = get_user($uid);
      echo "User {$user-&gt;name} has made a &lt;a href='{$comment-&gt;permalink}'&gt;comment&lt;/a&gt;";
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Thanks!</p>
  
  <p>EDIT:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>SELECT * FROM myplugin_transactions LEFT JOIN myplugin_meta ON myplugin_transactions.id = myplugin_meta.txn_id WHERE ( ... ? )
  </code></pre>