Historized links: introduction
A common problem in business-oriented database design is how to store the history of data as it changes. Tracking changes of attribute values is common, and is widely explained, see for example my previous post “Historized attributes: systematic table design”.
Let’s discuss a somewhat more complex scenario: historized relationships. An example of a historized relationship is somebody assigned to a project for a period of time, then assigned away, and reassigned again later. In some use cases we want to know the entire history: for example, for billing purposes.
In this introduction post we’ll outline the design space of this problem. It’s quite extensive, and the interesting thing about it is that it touches on many topics that are present in other database modeling situations.
The goal of this text is to teach you how to think about the practical variations of this task.
We’ll start with an outline of the full design space. As usual, we split the discussion into logical and physical levels. This helps to simplify thinking about the problem.
Design space outline
1. Logical level (requirements first):
- Cardinality (M:N and 1:N examples)
- Dates vs timestamps (inclusive vs exclusive range end)
- Scheduled assignments (past, current, planned)
- Bookkeeping timestamps (record added or changed)
2. Physical level (tables and indexes):
- Indexes (query performance)
- One table or two (current vs historical data)
- Primary keys (surrogate vs composite)
- NULL handling (sentinels vs sentinel-free)
- Database constraints (non-overlapping intervals)
3. Queries (query-driven design)
4. Migrations (adding history later)
Problem definitions and logical level
We want to discuss a general approach to this problem. We could choose abstract names such as A and B, but it’s easier to think about this if we have a concrete problem to discuss. That’s why we choose two specific examples (“Workers assigned to the Project” and “Teams and Team leads”).
Why two? Because they have two different cardinalities: M:N and 1:N. Cardinalities are the most important property of a specific link, and historized links are implemented differently depending on the cardinality.
You can generalize the implementation by just substituting your own anchors. To implement the historization of any link, apply the reasoning presented here, considering all the choices that your business requirements demand.
The first specific example would be the following:
Imagine a simple work tracking system. It uses two main entities: Workers and Projects. A worker can be assigned to a project for a certain amount of time.
Note that the same worker could be assigned to the same project several times in different periods of time. Examples:
- “Alice was assigned to project Foo from 2026-03-02 till 2026-03-13” (historical assignment);
- “Alice is assigned to project Foo from 2026-05-18” (current assignment).
Multiple workers can be assigned to a project, and a worker can have multiple projects assigned.
This is an example of M:N cardinality.
Variation: dates vs timestamps
Our example uses dates. A common variation here (for some other use case) would be to use timestamps. For example, imagine if you want to track the history of subscriber relationships between the users. When a user subscribes to another user and then unsubscribes, you may want to remember the fact that some time ago they were “friends”.
An important difference between dates and timestamps is that you would want to use different comparison operators for the end of the time interval:
- for dates, it’s less than or equal: $certain_date <= $assigned_till;
- for timestamps, it’s less than: $certain_timestamp < $timestamp_until;
Why? Because for dates, the interval is typically inclusive (“from 1st to 20th of March”). For timestamps, however, it makes sense to use an exclusive interval (“this access code is valid till April 15th, 20:00:00”).
Variation: scheduled assignments
In our example, we only track the past state and the current state. But even in this example, we may want to also implement scheduled assignments. Scheduled events are not guaranteed to happen, as opposed to the past information, so we need to treat it differently.
Suppose that today is 2026-06-01, and Alice is assigned to project Foo from 2026-05-18. We may want to schedule her end date, say for 2026-06-29. But this end date may change, or even be cancelled (we decide that Alice is assigned indefinitely, for the time being).
To handle this, we need a different logical model (and of course, a different physical model).
Also, it’s possible to have the entire assignment scheduled, not only the end date. Suppose that we plan to assign Bob to project Bar starting from 2026-06-20 until 2026-07-20. Anything can change: starting date, end date, and the entire assignment could be cancelled (maybe the project is cancelled, or Bob quits).
We need to be careful not to mix three types of data: historical, current, and planned.
Variation: 1:N cardinality
To illustrate the 1:N cardinality, we could use the “Teams and Team leads” example use case:
Imagine that we have software development teams. Each team can have a team lead. A team lead can handle several teams; one team can have only one team lead. It’s possible that a team does not have a team lead (a case of autonomous teams).
Links of 1:N cardinality are usually implemented as a column in the table that contains the N-side anchor. In this example, the team lead information would be stored in the “teams.team_lead_id” column. So, for example, it becomes more natural to move the historical information into a separate table. We’ll discuss this in a separate post.
Variation: bookkeeping timestamps
Very often we need to know when a certain database record was added or changed. For that we need to add precise bookkeeping timestamps.
Such timestamps live on the intersection between the logical and physical level, so to speak. So, they need to be discussed separately.
Physical level
As usual, before we discuss the physical level, we must make sure that we confirm business requirements (problem definition), and build the logical model based on those.
Physical level is where we discuss tables, columns and indexes.
Also, this is where you must know which database implementation you’re going to use. We assume a common modern relational database, such as Postgresql or MySQL, and we use a baseline table design strategy.
If you have a substantially different database, you may need a different table design strategy. More on that later.
Here is a list of table design variations that could be seen in the general practice.
- indexes;
- using two separate tables for the current and historical data, or only one?
- primary keys: single-column surrogate or composite multi-column PK?
- NULL values, sentinel values, or sentinel-free approach?
- database constraints: maintaining non-overlapping intervals;
Some of them are undoubtedly important, such as indexes.
For primary keys, NULL values and other database constraints, on the other hand, there is a lot of disconnect between textbooks and practice.
Queries
Generally when we design a database we’re more interested in where we’ll store all the data so that it is available in a clean way. We rely on the database capability to run queries against any schema. If the schema is sensible, the database will often manage to handle a broad range of possible queries.
Sometimes, though, we need to optimize the schema for a certain query or class of queries.
For this particular problem it makes sense to discuss the queries explicitly.
Database migrations
Sometimes the link is initially implemented without historization. Then, when the system is already up and running, we realize that we’d like to also track the history of the relationship. So, we need to do a database migration.
We need to discuss some concerns that arise specifically during the migration.
The series
All topics mentioned in this overview are discussed in a series of posts.
“Part 1. Project tracking system (M:N case)”
(to be published)
“Part 2. Teams and team leads (1:N case)”
(to be published)
“Database Design Book” (2025)
Learn how to get from business requirements to a database schema
If this post was useful, you may find this book useful too.
Table of contents and sample chapters
Book length: 145 pages, ~32.000 words. Available in both PDF and in EPUB format.
Subscribe here to receive updates: