title: Database - Designing an "Events" Table
body: <p>After reading the tips from <a href="http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/" rel="noreferrer">this great <strong>Nettuts+ article</strong></a> I've come up with a table schema that would separate highly volatile data from other tables subjected to heavy reads and at the same time lower the number of tables needed in the whole database schema, however I'm not sure if this is a good idea since it doesn't follow the rules of normalization and I would like to hear your advice, here is the general idea:</p>
  
  <hr>
  
  <p>I've four types of users modeled in a <a href="http://martinfowler.com/eaaCatalog/classTableInheritance.html" rel="noreferrer">Class Table Inheritance</a> structure, in the main "user" table I store data common to all the users (<code>id</code>, <code>username</code>, <code>password</code>, several <code>flags</code>, ...) along with some <code>TIMESTAMP</code> fields (<code>date_created</code>, <code>date_updated</code>, <code>date_activated</code>, <code>date_lastLogin</code>, ...).</p>
  
  <p>To quote the tip #16 from the Nettuts+ article mentioned above:</p>
  
  <blockquote>
    <p><strong>Example 2</strong>: You have a “last_login”
    field in your table. It updates every
    time a user logs in to the website.
    But every update on a table causes the
    query cache for that table to be
    flushed. You can put that field into
    another table to keep updates to your
    users table to a minimum.</p>
  </blockquote>
  
  <p>Now it gets even trickier, I need to keep track of some user statistics like</p>
  
  <ul>
  <li>how many <strong>unique</strong> times a user profile was seen</li>
  <li>how many <strong>unique</strong> times a <strong>ad from a <em>specific type of user</em></strong> was clicked</li>
  <li>how many <strong>unique</strong> times a <strong>post from a <em>specific type of user</em></strong> was seen</li>
  <li>and so on...</li>
  </ul>
  
  <p>In my fully normalized database this adds up to about 8 to 10 additional tables, it's not a lot but I would like to keep things simple if I could, so I've come up with the following "<code>events</code>" table:</p>
  
  <pre><code>|------|----------------|----------------|---------------------|-----------|
  | ID   | TABLE          | EVENT          | DATE                | IP        | 
  |------|----------------|----------------|---------------------|-----------|
  | 1    | user           | login          | 2010-04-19 00:30:00 | 127.0.0.1 |
  |------|----------------|----------------|---------------------|-----------|
  | 1    | user           | login          | 2010-04-19 02:30:00 | 127.0.0.1 |
  |------|----------------|----------------|---------------------|-----------|
  | 2    | user           | created        | 2010-04-19 00:31:00 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 2    | user           | activated      | 2010-04-19 02:34:00 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 2    | user           | approved       | 2010-04-19 09:30:00 | 217.0.0.1 |
  |------|----------------|----------------|---------------------|-----------|
  | 2    | user           | login          | 2010-04-19 12:00:00 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 15   | user_ads       | created        | 2010-04-19 12:30:00 | 127.0.0.1 |
  |------|----------------|----------------|---------------------|-----------|
  | 15   | user_ads       | impressed      | 2010-04-19 12:31:00 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 15   | user_ads       | clicked        | 2010-04-19 12:31:01 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 15   | user_ads       | clicked        | 2010-04-19 12:31:02 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 15   | user_ads       | clicked        | 2010-04-19 12:31:03 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 15   | user_ads       | clicked        | 2010-04-19 12:31:04 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 15   | user_ads       | clicked        | 2010-04-19 12:31:05 | 127.0.0.2 |
  |------|----------------|----------------|---------------------|-----------|
  | 2    | user           | blocked        | 2010-04-20 03:19:00 | 217.0.0.1 |
  |------|----------------|----------------|---------------------|-----------|
  | 2    | user           | deleted        | 2010-04-20 03:20:00 | 217.0.0.1 |
  |------|----------------|----------------|---------------------|-----------|
  </code></pre>
  
  <p>Basically the <code>ID</code> refers to the primary key (<code>id</code>) field in the <code>TABLE</code> table, I believe the rest should be pretty straightforward. One thing that I've come to like in this design is that I can keep track of all the user logins instead of just the last one, and thus generate some interesting metrics with that data.</p>
  
  <p>Due to the growing nature of the <code>events</code> table I also thought of making some optimizations, such as:</p>
  
  <ul>
  <li><strong>#9</strong>: Since there is only a finite number of tables and a finite (and predetermined) number of events, the <code>TABLE</code> and <code>EVENTS</code> columns could be setup as <code>ENUM</code>s instead of <code>VARCHAR</code>s to save some space.</li>
  <li><strong>#14</strong>: Store <strong><code>IP</code>s as <code>UNSIGNED INT</code>s</strong> with <code>INET_ATON()</code> instead of <code>VARCHAR</code>s.</li>
  <li>Store <strong><code>DATE</code>s as <code>TIMESTAMP</code>s</strong> instead of <code>DATETIME</code>s.</li>
  <li>Use the <strong><code>ARCHIVE</code></strong> (<strike>or the <code>CSV</code>?</strike>) engine instead of <code>InnoDB</code> / <code>MyISAM</code>.
  
  <ul>
  <li>Only <code>INSERT</code>s and <code>SELECT</code>s are supported, and data is compressed on the fly.</li>
  </ul></li>
  </ul>
  
  <p>Overall, each event would only consume 14 (uncompressed) bytes which is okay for my traffic I guess.</p>
  
  <h2>Pros:</h2>
  
  <ul>
  <li>Ability to store more detailed data (such as logins).</li>
  <li>No need to design (<em>and code for</em>) almost a dozen additional tables (dates and statistics).</li>
  <li>Reduces a few columns per table and keeps volatile data separated.</li>
  </ul>
  
  <h2>Cons:</h2>
  
  <ul>
  <li>Non-relational (still not as bad as EAV):
  
  <ul>
  <li><code>SELECT * FROM events WHERE id = 2 AND table = 'user' ORDER BY date DESC();</code></li>
  </ul></li>
  <li>6 bytes overhead per event (<code>ID</code>, <code>TABLE</code> and <code>EVENT</code>).</li>
  </ul>
  
  <hr>
  
  <p>I'm more inclined to go with this approach since the pros seem to far outweigh the cons, but I'm still a little bit reluctant... <strong>Am I missing something? What are your thoughts on this?</strong></p>
  
  <p><strong>Thanks!</strong></p>
  
  <hr>
  
  <h2>@coolgeek:</h2>
  
  <blockquote>
    <p>One thing that I do slightly
    differently is to maintain an
    entity_type table, and use its ID in
    the object_type column (in your case,
    the 'TABLE' column). You would want to
    do the same thing with an event_type
    table.</p>
  </blockquote>
  
  <p>Just to be clear, you mean I should add an additional table that maps which events are allowed in a table and use the PK of that table in the events table instead of having a <code>TABLE</code> / <code>EVENT</code> pair?</p>
  
  <hr>
  
  <h2>@ben:</h2>
  
  <blockquote>
    <p>These are all statistics derived from
    existing data, aren't they?</p>
  </blockquote>
  
  <p>The additional tables are mostly related to statistics but I the data doesn't already exists, some examples:</p>
  
  <pre><code>user_ad_stats                          user_post_stats
  -------------                          ---------------
  user_ad_id (FK)                        user_post_id (FK)
  ip                                     ip
  date                                   date
  type (impressed, clicked)
  </code></pre>
  
  <p>If I drop these tables I've no way to keep track of who, what or when, not sure how views can help here.</p>
  
  <blockquote>
    <p>I agree that it ought to be separate,
    but more because it's fundamentally
    different data. What someone is and
    what someone does are two different
    things. I don't think volatility is so
    important.</p>
  </blockquote>
  
  <p>I've heard it both ways and I couldn't find anything in the MySQL manual that states that either one is right. Anyway, I agree with you that they should be separated tables because they represent kinds of data (with the added benefit of being more descriptive than a regular approach).</p>
  
  <blockquote>
    <p>I think you're missing the forest for
    the trees, so to speak.</p>
    
    <p>The predicate for your table would be
    "User ID from IP IP at time DATE
    EVENTed to TABLE" which seems
    reasonable, but there are issues.</p>
  </blockquote>
  
  <p>What I meant for "not as bad as EAV" is that all records follow a linear structure and they are pretty easy to query, there is no hierarchical structure so all queries can be done with a simple <code>SELECT</code>.</p>
  
  <p>Regarding your second statement, I think you understood me wrong here; the IP address is not necessarily associated with the user. The table structure should read something like this:</p>
  
  <blockquote>
    <p>IP address (<code>IP</code>) did something
    (<code>EVENT</code>) to the PK (<code>ID</code>) of the
    table (<code>TABLE</code>) on date (<code>DATE</code>).</p>
  </blockquote>
  
  <p>For instance, in the last row of my example above it should read that IP 217.0.0.1 (some admin), deleted the user #2 (whose last known IP is 127.0.0.2) at 2010-04-20 03:20:00.</p>
  
  <blockquote>
    <p>You can still join, say, user events
    to users, but you can't implement a
    foreign key constraint.</p>
  </blockquote>
  
  <p>Indeed, that's my main concern. However I'm not totally sure what can go wrong with this design that couldn't go wrong with a traditional relational design. I can spot some caveats but as long as the app messing with the database knows what it is doing I guess there shouldn't be any problems.</p>
  
  <p>One other thing that counts in this argument is that I will be storing much more events, and each event will more than double compared to the original design, it makes perfect sense to use the <code>ARCHIVE</code> storage engine here, the only thing is it doesn't support <code>FK</code>s (neither <code>UPDATE</code>s or <code>DELETE</code>s).</p>