StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Im actually working on a similar system so Im interested in the answers you get. </p> <p>For my project, having a full historical accounting was not important so we chose to keep the table fairly lean much like what youre doing. Our tables look something like this:</p> <pre><code>CREATE TABLE `activity_log_entry` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `event` varchar(50) NOT NULL, `subject` text, `publisher_id` bigint(20) NOT NULL, `created_at` datetime NOT NULL, `expires_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `event_log_entry_action_idx` (`action`), KEY `event_log_entry_publisher_id_idx` (`publisher_id`), CONSTRAINT `event_log_entry_publisher_id_user_id` FOREIGN KEY (`publisher_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 </code></pre> <p>We decided that we dont want to store history forever so we will have a <code>cron</code> job that kills history after a certain time period. We have both <code>created_at</code> and <code>expired_at</code> columns simply out of convenience. When an event is logged these columns are updated automatically by the model and we use a simple <code>strftime('%F %T', strtotime($expr))</code> where <code>$expr</code> is a string like <code>'+30 days'</code> we pull from configuration.</p> <p>Our <code>subject</code> column is similar to your <code>callback</code> one. We also chose not to directly relate the subject of the activity to other tables because there is a possibility that not all event subjects will have a table, additionally its not even important to hold this relationship because the only thing we do with this event log is display activity feed messages. We store a serialized value object of data pertinent to the event for use in predetermined message templates. We also directly encode what the event pertained to (ie. profile, comment, status, etc..).</p> <p>Our <code>events</code> (aka activities.) are simple strings like <code>'update'</code>,<code>'create'</code>, etc.. These are used in some queries and of course to help determine which message to display to a user.</p> <p>We are still in the early stages so this may change quite a bit (possibly based on comments and answers to this question) but given our requirements it seemed like a good approach. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload