07
Mar
Temporal Shift
One of the most common “design patterns” in data modelling is the temporal shift. It is basically used for historical data, but is not limited to this. Despite this “good practice” it is staggering how many “would be data modellers” do not use it, opting for rather more inflexible solutions that are not future proof.
Take for instance the standard address person relationship. A person has an address, and an address can have many people (in the case of multiple people at a registered office, or domestic residence (note that address structures are discussed in more detail here) But if we only record the address now, what happens when the person moves? Some people allow fields for previous address in the Person table. Not only is this poor design (in that it allows you to only record one previous address) it is also bad practice.
The Person table is there to store information about the person. No more. So what we say is that in fact, the person has many addresses OVER TIME. This can be achieved simply by adding two date fields, ‘VALID_FROM’ and ‘VALID_TO’ If the VALID_TO field is null or in the future the row is current.
Fig 1
Obviously the Person table and Address table have primary keys, as should the link table PERSON_ADDRESS The description of the link table that does the business is here
So if we had a peek at the data
| PERSON_ADDRESS_ID | PERSON_ID | ADDRESS_ID | VALID_FROM | VALID_TO |
|---|---|---|---|---|
| 1 | 1 | 1 | 01/01/1970 | 01/01/1980 |
| 2 | 1 | 2 | 02/01/1980 | 01/01/1990 |
| 3 | 1 | 3 | 02/01/1990 |
So hopefully you can see that PERSON_ID 1 has lived at 3 residences over the past 38 years, and is currently residing at ADDRESS_ID 3, as the VALID_TO is still NULL This also has an interesting mirror image scenario where it is now possible to see who lived at a particular address over time. Useful for local councils, services companies and so on. This kind of temporal shift is not limited to historical data. If the VALID_FROM and VALID_TO dates are in the future, it would be “will live at” rather than “lived at”. This would be really slick if a customer wanted their mail forwarded to their holiday home for two weeks, and by incorporating this kind of flexible and supple design into your data model it opens doors for future proofing that otherwise would not have been there.
- Login to post comments
Comments
Temporal Shift
I agree that this is a good general way to model people and their addresses, but I think its suitability depends on the use that the data will be put to.
Good questions to ask here are:
If you answer yes, then this kind of structure will be very useful. If you are 'just' building a mailing list then you will answer no and can consider a more denormalised table containing both person and address information. This technique applies equally to all personal attributes that may change over time, e.g contact numbers, marital status, salary.
G
Temporal data
Good point you raise. Certainly within any good data model there should be justification for including something. The article here was demonstrating how to track temporal changes in data, using addresses as an example. I would urge the reader to pull up a few thousand feet and see the design pattern and not the data for a second, then zoom back in and start thinking about which pieces of data this is applicable to. The temptation with any new design concept is to throw it at anything that moves, but you are right to 'test' the requirements with those two questions.
I guess the question could now be, does this piece of data about this entity change over time, and if so do we wish to record this change and keep all historical data? If the answer to this is yes then the above design pattern applies