Temporal data

Many tables in a database house what I will call temporal data. This data is related to the main objects of the database and changes over time. For example, in a database about people it includes: data about where a person lives, information about who a person works for, and what a person's name is. Each of these data may be different at different moments in time. In this modern age even a person's gender might change during their lifetime. It is necessary that temporal data be maintained as such, if a database is to model reality.

The places where people live shouldn't go away when a person moves; the companies that people work for don't go out of existence when the only person in the database who works for them, leaves them. If these places and companies exist in the real world, and are ever associated with the objects for which the database exists, they should be represented in the database. Even when they do cease to exist, the fact that at some time they did exist, should be reflected in a database. If these entities exist only once in the real world then they should exist only once in the database.

Consider the data stored about people: only a person's date of birth and date of death are static. Everything else about a person is temporal. Each person only requires an identifier and a date of birth! A person's names are temporal; at birth the person's parents usually give them names, but over a life-time the person's names may change. A name history is needed; a table storing the person's names for the duration of their time spent on this planet. Not only will this track legal changes of name but it may also be used for aliases. Each name-history entry will have to include a start date and optionally, a stop date. Again, we are trying to model reality – and people's names do change. People's names are also ordered and in some cultures they take a large number of names.

If your database is conscientiously updated all the time, and doesn't have a name history, then how will you find that undergraduate named Maxine Smythe who was in the class of 1972? She has had two husbands since you were class-mates, is now single, and has legally changed her name to something quite different. Keep your database up to date, but don't throw away the history. These temporal data can be very valuable.

The excuse used by most database designers is that for all practical purposes the data in their database may be considered static. I have yet to see one instance of a database, that has a lifespan of more than a few weeks, for which this is true! There is very little static data in most databases. Most of the data stored in most databases is temporal data.