A MySQL Time Machine

N.B. This post is required for MySQL 5.1 (or earlier) and will likely be needed for newer editions of the RMDBS as well

Did Doc Emmet Brown not invent time travel 10 years before MySQL was initially released?

He did and the laws of known physics haven’t been broken yet, what I am explaining here is representing temporal state of an element (e.g an account) whose historical state can be queried for any given point of time and future state(s) can be set in advance.

 

image

In the above illustration the Account is active for the timestamp of the query, Green represents an active account

The tables to be used in this effort will look similar to this

image

Account table

image

Account actions table

The account table has the usual fields:

  • Account_ID
  • Name
  • Credit card number
  • etc

The  account_actions table records :

  • A unique action ID
  • An Action_Type – this is an enumeration of an account action
  • An Action_Date – the point where this account action takes come into effect
  • Created_Date – the date that this action was created

Can I not simply do this with a JOIN and a WHERE?

Oh like this?

 1: SELECT
 2:     Account.Name,Account_actions.Action_Type
 3: FROM
 4:     Account
 5: JOIN
 6:     Account_actions
 7: ON
 8:     account.Account_ID = Account_actions.Account_ID
 9: WHERE
 10:     Account_actions.Action_Date < NOW();
 11: 

In the above example we hope to obtain the account name from the Account table and the previous action_type from the Account_Actions table with the account action corresponding to the most recent one, this will produce erroneous results as that JOIN operation does not apply sorting of the results from the joined table to the join even if we were to add an ORDER BY.

In most cases the Action_Type returned would likely be the first one stored into the Account_actions table in the subset of data created from that WHERE clause.

It is entirely possible that this erroneous implementation could lay in wait ready to trip up even experienced SQL Query Craftspeople as it seems to be a correct logical Command

Is there a solution?

Yes in place of joining a pre-existing table we JOIN a table generated by an inline SELECT command which has the necessary sorting and filtering in place.

So to correctly implement this goal we would use the following command

 1: SELECT
 2:     Account.Account_ID, Generated_account_actions.Action_Type
 3: FROM
 4:     Account
 5: JOIN
 6:     (
 7:         SELECT
 8:             scheduled_actions.Account_ID, scheduled_actions.Action_Type
 9:         FROM
 10:             scheduled_actions
 11:         WHERE
 12:             scheduled_actions.Action_Date < NOW()
 13:         ORDER BY
 14:             scheduled_actions.Action_Date DESC
 15:     ) AS Generated_account_actions
 16: ON
 17:     account.Account_ID = Generated_account_actions.Account_ID;

This query uses a the results from the filtered and ordered table (under an alias of Generated_account_actions) to perform a JOIN and return the most recent action state for this user account.

I hope this post helps you when making your next killer MySQL backed App, these queries may be performance optimised but I wish to illustrate a point.


Leave a Reply

Your email address will not be published / Required fields are marked *

y65la0

Please type the text above: