ID design and primary keys, pt. 2
Author: Alexey Makhotkin squadette@gmail.com, (~2200 words)
In the first part we introduced a strict separation between logical and physical aspects around IDs and primary keys.
We discussed the logical concepts of external IDs and anchor IDs, and the physical concept of a primary key.
There are four main data elements: anchors, attributes, links, and secondary data. Every database is modeled using a combination of those elements. Elements map to physical tables, and each physical table needs a primary key.
In this post we’ll present a catalog of ways primary keys are used to design tables. Here is a short list of distinct designs that we’ll discuss below:
- anchor tables (with attributes and 1:N links): simple PK;
- attributes: Entity-Attribute-Value;
- M:N links: junction tables;
- anchor tables: composite PK;
- secondary data scenarios.
Table of contents
Subscribe here to receive updates:
How did we arrive at this list?
Anchors are most commonly implemented as a table with a simple PK. (This example was shown in the “Simple anchor tables” section of part 1.)
Sometimes several anchors can coexist in the same physical table.
Attributes most commonly live in the same table as their anchor, and reuse its primary key. Some attributes also go into side tables, but they too use the same primary key format.
However, quite often attributes are implemented using the Entity-Attribute-Value approach (EAV). In this approach, a separate table is used, with a very specific primary key design.
1:N links are similar to attributes: they most commonly live in the same table as their anchor, and reuse its primary key. Some may also go into side tables, with the same primary key format.
Interestingly, in some scenarios 1:N links can affect the primary key design of their N-side anchors. This is a topic for part 3: composite primary keys for anchor tables.
M:N links virtually always live in a junction table with a composite PK.
Secondary data is ad-hoc by definition. In the most common scenarios primary keys follow directly from what a row represents. However, there is also a long tail of application-specific designs.
Grouping similar designs, we get half a dozen main constructions. We’ll discuss each in its own section.
Anchor tables: simple PK
┌──────────────────────────────┐
│ users │
├──────────────────────────────┤
│ PK user_id bigint │
│ · · · · · · · · · · · · · · │
│ first_name varchar │
│ date_of_birth date │
│ -- other attributes │
│ -- or 1:N links │
└──────────────────────────────┘
This is the simplest and most ubiquitous pattern.
The user_id column is just a sequential number without a specific business meaning. We use it so that there is never a need to change this value.
Attributes: Entity-Attribute-Value
┌───────────────────────────────┐
│ restaurant_attributes │
├───────────────────────────────┤
│ PK ┌ restaurant_id bigint │
│ └ attr_name varchar │
│ · · · · · · · · · · · · · · · │
│ attr_value integer │
└───────────────────────────────┘
In some business domains you may need lots of similar attributes that have the same type. One real-world example is presented in my post “Many yes/no attributes: table design study”. In this example, restaurants have many important properties such as: “Is it vegan-only? Is it wheelchair-accessible? Is it on the rooftop?” etc.
Technically we could implement this in the normal way, just adding more and more columns to the “restaurants” table. This, however, requires changing the table structure every time a new attribute is added or removed. To circumvent this, people invented the Entity-Attribute-Value approach.
We create a table that contains exactly three columns:
- anchor ID (in this example, Restaurant);
- attribute name (a short machine-readable string, e.g.
"is_vegan", or a predefined integer constant); - attribute value;
All attributes that are stored in such a table need to be implemented by the same physical type (in this case, yes/no attributes are implemented as integers). To define a new attribute, you just need to define a new string identifier such as "is_rooftop", or allocate another constant ID. Your application needs to use that identifier to deal with this attribute.
Some EAV designs are even more dynamic: the attribute value column would have a string type, and contain values of different types: integer, monetary, string, yes/no, etc. The values would just be encoded as strings. This approach is used, for example, to handle plugin-specific metadata in WordPress.
We do not specifically endorse this design, but you will see it everywhere, and it’s relevant to our current topic: primary keys.
The primary key in the EAV table is composite: anchor ID and attribute name.
This makes sure that for each anchor ID there is at most one value for a certain attribute.
M:N links: junction tables
┌───────────────────────────────┐
│ project_workers │
├───────────────────────────────┤
│ PK ┌ project_id integer │
│ └ worker_id integer │
│ · · · · · · · · · · · · · · · │
│ -- link attributes │
└───────────────────────────────┘
This is an extremely common case that implements many-to-many relationships.
Suppose that you need to track workers assigned to projects. A worker can be assigned to several projects, and a project can have several workers assigned. We call these M:N links.
In Minimal Modeling, a link by definition requires that every pair of anchor IDs be unique. (There could be several different links between the same two anchors, of course.)
The most common way to implement an M:N link is with a so-called junction table. The first two columns of this table hold the first anchor ID and the second anchor ID, in our case: “project_id” and “worker_id”.
To implement pairwise uniqueness, we could use either a composite primary key or a uniqueness constraint. In this section we’ll discuss the former approach. (The latter implementation via the uniqueness constraint and another surrogate primary key will be discussed in the “Nitpicking” part.)
We’ll just use the composite primary key made from both anchor IDs — it will give us the required pairwise uniqueness: (project_id, worker_id).
As we’re discussing the physical design, let’s just mention that you most probably also need to add a table index on the second anchor ID in the pair. A canonical table structure for the junction table would be:
CREATE TABLE project_workers (
project_id INTEGER NOT NULL,
worker_id INTEGER NOT NULL,
PRIMARY KEY (project_id, worker_id)
);
CREATE INDEX ON project_workers (worker_id);
This is the first time we mention indexes, but forgetting an index here is such a common mistake that it needs to be covered to prevent embarrassment. We’ll discuss this in detail later in the series.
Here is a bonus question: what should be the order of columns in the primary key? (project_id, worker_id) or (worker_id, project_id)? Conceptually it doesn’t matter, but in some rare cases you could see some performance wins. We’ll discuss the performance aspects of primary keys later in the series.
Anchor tables: composite PKs
┌──────────────────────────────┐
│ order_items │
├──────────────────────────────┤
│ PK ┌ order_id integer │
│ └ position integer │
│ · · · · · · · · · · · · · · │
│ item_id integer │
│ qty integer │
└──────────────────────────────┘
This is the topic that started the entire series. Initially I thought that I’d be able to do a quick write-up on this, but it took two abandoned drafts (1900 words in the first draft, 3700 words in the second one) before I found the right sequence that you’re now reading.
We’ll discuss this in detail in part 3, but here is a very brief outline.
Sometimes one anchor is “nested” in another anchor. Let’s take two anchors, Order and OrderItem. OrderItem is fully contained in Order: an order item cannot exist without an order.
We can identify each order item using a sequential number stored in the “position” column, starting at 1 for each new order (1, 2, 3, …).
Remembering the anchor ID requirements from part 1, we see that in principle, we could use a combination of Order ID and numeric position as an anchor ID for OrderItem.
Implementing this, we would arrive at an anchor table with a composite primary key: (order_id, position).
We do not specifically endorse this design, but it’s important if you want to better understand classic relational modeling. In particular, in part 3 we’ll discuss what would happen if you decide to use such a primary key in a real-world database.
Secondary data
Aggregations
┌──────────────────────────────┐
│ yearly_sales │
├──────────────────────────────┤
│ PK ┌ sales_year integer │
│ └ customer_id integer │
│ · · · · · · · · · · · · · · │
│ amount decimal │
│ orders_cnt integer │
└──────────────────────────────┘
Here is a very common textbook pattern of aggregated data. Suppose that we’re building a dashboard that displays nice-looking sales graphs: monetary amount and the number of orders, grouped by the calendar year and the customer ID.
We could use a direct GROUP BY query against the “orders” table:
SELECT YEAR(placed_at) AS sales_year, customer_id,
SUM(total) AS amount, COUNT(*) AS orders_cnt
FROM orders
GROUP BY YEAR(placed_at), customer_id
However, if you have a lot of orders then this query can become too slow. A common solution is to maintain a pre-aggregated table with those four columns, and keep it updated as new orders are placed or updated. Such a table could be called something like “yearly_sales”, and it contains just the four columns.
We want to have just one row per year per customer, so we can implement this directly by choosing a composite primary key on the corresponding columns: (sales_year, customer_id).
We could have multiple different slices of data, for example:
- per day per country;
- per year/month without further grouping;
- per industry per day per item category;
- and so on, depending on whatever our business requires.
Each slice could live in a separate table, and each table would have a primary key that directly corresponds to the grouping.
Flat tables
┌────────────────────────────────┐
│ orders_flat │
├────────────────────────────────┤
│ PK order_id bigint │
│ · · · · · · · · · · · · · · · │
│ placed_at timestamp │
│ customer_id integer │
│ total_amount decimal │
│ customer_country varchar │
│ -- and so on │
└────────────────────────────────┘
Another common pattern of secondary data is creating denormalized tables based on a certain anchor. In this example, we create a table “orders_flat” that contains a copy of information about orders, including pieces of data that are normally stored in different tables.
For example, “total_amount” is a sum of costs of items included in this order. The primary information would be stored in the “order_items” table. “customer_country” is a copy of information from the “customers” table, and so on.
Any secondary data is always created for a specific purpose. Its design is mainly driven by concerns of query performance or human convenience.
Flat tables are denormalized to simplify analytical queries. When you query the normalized database, you need to join some tables together. Flat tables are implemented by pre-joining, so you can avoid some of the joins. Flat tables also use pre-aggregation, like in this case with the “total_amount” column.
We do not specifically endorse this design, but it’s very common, and it’s relevant to our current topic: primary keys.
Flat tables usually inherit the primary key from their base table. In this case, it’s just “order_id”, same as in the “orders” table.
Ad-hoc primary keys
As we mentioned before, primary keys exist only on a physical level. In some cases primary keys are closely aligned to the concept of object identity, but it’s important not to confuse the two.
Primary keys could be so ad-hoc that they become hard to classify. We can only show specific examples and explain why primary keys are used in a specific way.
At the same time, there is no full list of such examples, exactly because they are ad-hoc. It’s just that at some point you realize that you can do a clever trick with primary keys, and you do it.
Here is one example: “Subtypes and status-dependent data: pure relational approach”. The primary keys are unusual, and they are implemented this way to serve as a foundation for foreign keys that are also designed in an unusual way.
In the following posts we’ll show two more examples of primary keys that go against the grain of the textbook approach. First, we’ll exchange the primary key and a different unique key and see what happens. Second, we’ll show an anchor table with a composite primary key that does not need nested uniqueness, and discuss why this could be a useful construction.
I’m sorry to be vague here, but this specific section is really a catch-all for the long tail of exotic designs. To be understood, they need to be discussed in detail, or they do not make a lot of sense.
“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.
Conclusion
We’ve discussed half a dozen distinct primary key designs that cover virtually all the database design cases:
- anchor tables: simple PK;
- attributes: Entity-Attribute-Value (EAV);
- M:N links: junction tables;
- anchor tables: composite PK;
- secondary data scenarios: aggregations and flat tables;
- exotic ad-hoc primary keys;
Primary keys are often taught as a basic concept of database design. Here we show that primary keys are useful only for the physical design. They do not exist on the logical level where anchor IDs and external IDs operate. This is probably the main contribution of this series of posts.
When you design a database, you start with the logical model. Only when you’ve decided on which database server to use, and which table design strategy to apply, can you start thinking about primary keys.
In some cases primary keys are close to logical identity (the case of anchor tables and M:N links), but then they become more and more technical and ad-hoc (anchor tables with composite PKs, EAV, secondary data, not to mention exotic variants).
In part 3 we’re going to discuss anchor tables with composite PKs.
I’d be happy to hear your feedback and questions:
Alexey Makhotkin
squadette@gmail.com.