SSWUGtv
with Stephen Wynkoop
Simplify Managing your systems: local/private systems, on the cloud – or wherever! Watch this edition of SSWUGtv as Don Boxley from DH2i brings you solutions.
Watch the Show
Flights, Dates and Times
The last couple of days we have been talking about Business Rules and where to apply them. An example of an Airline Flight was used to demonstrate the need for Business Rules in data definitions as well as application validation. The intention of one business rule was that a flight cannot end before it begins. This makes sense in our current understanding of time, until you take into consideration different time zones throughout the world.
Many have written in on this discussion with comments such as this one from Bastien:
Another thing to consider with the rules is that you can fly back across the international date line and you could in theory have an landing date before you take off.
Or this comment from Bryan:
I once took a flight that spent 12 hours in the air but the landing time was about 20 minutes after the takeoff time. Crossing the International Date Line messes with you in more ways than one! A flight between two places a little closer together (or a faster plane) could have resulted in the (local) landing time and even the next flight’s departure time being “before” the first (local) takeoff time, so the only way you could really enforce that business rule is if you were sure all the flight times were based on the same time zone (GMT / UT probably). Another fine example of the need to be extra careful that your business rules take into consideration and properly handle boundary conditions!
Jonathan has experienced the same thing, and still acknowledges the value of the constraints:
Don’t forget about flights over the international data line. I left Sydney australia on July 1st and landed in Hawaii on June 30th as I crossed over the international date line.
I agree, we often import data from other systems and need to ensure that the integrity of the information is kept intact. It makes sense to cover all the entry points and enforcing it at the source ensures that. Of course it also makes sense to put many of the checks in the front end for the reasons you have already stated.
I don’t think that many would argue that a plane could land before it takes off in normal time/space found here on earth. So, how do we accommodate this phenomena? Are there any techniques we may use whereby our data can be considered pristine and still implement this very real rule?
Bryan touched on one such example by converting all times to GMT or only using a single time zone when entering data, translating the local time of departure and/or arrival to be within the same time zone. Using this technique the check rule would work.
Another approach would be to have a time zone from which everything is managed (GMT is a norm in this case) and to have a separate column for the offset from the application time zone. So, the departure and arrival columns are compared using the offset of each from the standard.
Here are a couple possible schemas from which valid check constraints may be made…you might choose one or the other based on your intended usage.
CREATE TABLE Flight1 (
FlightID INT NOT NULL IDENTITY(1,1)
,DepartureDateTime DATETIME NOT NULL
,DepartureOffset INT NOT NULL
,ArrivalDateTime DATETIME NOT NULL
,ArrivalOffset INT NOT NULL
,CONSTRAINT ckFlightDate CHECK (DATEADD(hh, DepartureOffset, DepartureDateTime) < DATEADD(hh, ArrivalOffset, ArrivalDateTime))
)
CREATE TABLE Flight2 (
FlightID INT NOT NULL IDENTITY(1,1)
,DepartureDateTime DATETIME NOT NULL
,DepartureGMT DATETIME NOT NULL
,ArrivalDateTime DATETIME NOT NULL
,ArrivalGMT DATETIME NOT NULL
,CONSTRAINT ckFlightGMT CHECK (DepartureGMT < ArrivalGMT)
)
Dates and times in a database are areas of special interest to me. Why not write in with examples of how you might solve this kind of requirement in your schemas? Send your comments to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Stop working 7 Days a Week to Get Your Job Done
In the previous newsletter article on Professional Development, we covered some office conflicts (don’t miss another professional newsletter tip—signup for the free newsletter here). A follow-up client question (regarding the previous newsletter article) was “If your workplace is getting too hard, but you like the job – do you leave the job or stay there? What’s your recommendation, please.”
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)