Design keeping track of activity - Database Administrators Stack Exchange most recent 30 from 2019-12-10T08:00:51Z 0 Design keeping track of activity Jason 2017-09-29T14:08:24Z 2017-10-03T16:19:17Z <p>I want to keep track of different activities/actions made by members of a website. For example "post created", "successful login", "file uploaded" etc.</p> <p>Is it OK to just have a table named for example "actions" and save timestamps , user ID etc and the primary key for the actions such as post_id (table Posts), file_id (table Files) </p> <p>Is this a valid way of doing it, or is there a better way?</p> 0 Answer by Károly Nagy for Design keeping track of activity Károly Nagy 2017-10-03T16:19:17Z 2017-10-03T16:19:17Z <p>Your proposed design is completely viable. </p> <p>Couple of things to consider:</p> <ul> <li>the table most likely will be write (INSERT) heavy </li> <li>will grow indefinitely unless archived </li> <li>queries most likely fall under OLAP and not OLTP (aggregations over point queries)</li> </ul> <p><strong>Workload</strong></p> <p>Assuming InnoDB engine you want primary key to be a continuously incrementing id so INSERT won't be random causing too much page split and fragment the tablespace.</p> <p>Benefits of <code>AUTO_INCREMENT</code>:</p> <ul> <li>Always incrementing</li> <li>Providing fast insert rate</li> </ul> <p>Downsides of <code>AUTO_INCREMENT</code>:</p> <ul> <li>One extra column </li> <li>You probably will never query on PK</li> </ul> <p>Timestamp would be a great candidate for PK if you can keep it <code>UNIQUE</code> since it's most likely will participate in most of your queries and most like as a range. (PK is at the end of every secondary key).</p> <p>You could also experiment with the an <code>id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY</code> instead. Use whichever gives better performance.</p> <p><strong>Size</strong></p> <p>Try to keep the row size as small as possible to avoid sharding/partitioning/archiving as long as possible (eventually you will need to but MySQL is quite good with tens of millions of rows even).</p> <p>To achieve this use only IDs in this table and have lookup tables where necessary. For example: instead of <code>action VARCHAR(12)</code> use <code>action_id TINYINT UNSIGNED</code>. <code>TINYINT</code> is 1 byte whereas action will be always at least 2 assuming <code>ASCII</code>, <code>latin1</code> or similar small character set but longer the string becomes the more byte it will use obviously. <code>UTF8</code> can use up to 4 bytes / character. </p> <p>Only put index on columns where you really need to. Daily, monthly, etc aggregation tables will serve you better than heavy indexes on this table.</p> <p><strong>A possible schema</strong> </p> <pre><code>CREATE TABLE my_action_log ( ts TIMESTAMP(3) PRIMARY KEY, action_id TINYINT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, post_id INT UNSIGNED NOT NULL, ... KEY ... # Whatever index you need ) ENGINE=InnoDB </code></pre>