maandag 5 januari 2015

Your database knows your data. Use it!

Case in point: an application that prints invoices. Some of these invoices have not been paid before the expiration date and must be printed in red.

That's easy enough; just write an IF statement in the application to compare the date the invoice was sent to the date it should have been paid on, and voila.

Except; that requires that the application has a function that can compare the dates that the database puts put. The database returns strings, so you must either SELECT the dates in a format that is purely numeric and in UTC, or one that requires no effort to convert into the programming language's objects.

Alternatively, you could use the query to *ASK* the database if the invoice has expired. No, not by running a separate query, but by augmenting the data that you already have in the table.

For example; if your table holds an id, price, paid, date_sent, and date_expired, you can do something like:

SELECT id, price,
  CASE WHEN NOW() > date_expired AND paid = FALSE
    THEN 1
    ELSE 0
  END AS expired
FROM ....

Now the data about the invoice contains a virtual-column called 'expired' which holds '1' for invoices that have expired, or '0' if they have not.

Stick this in a (materialized) view, and your application does not even need to know the businessrules about when and how invoices expire.

PS: Yes, of course, you could also run a cronjob at 00:00:00 that does this check and modifies the status of the invoice. That will work just fine if performance is an issue.