ID design and primary keys, pt. 3

Author: Alexey Makhotkin squadette@gmail.com, (~2900 words)

Welcome to part 3 of the “ID design and primary keys” series. Let’s investigate composite primary keys on anchor tables.

We’ll cover the following aspects of this table design approach:

• when is it even possible to use composite PKs in anchor tables;
• why would you want to do that: performance aspects;
• a conflict between the rigid physical implementation and the evolution of business requirements;

Table of contents

See also: Part 1, Part 2.

Subscribe here to receive updates:

Business requirements

As usual, we start with business requirements, using natural language.

Suppose that we build a tiny blog system. Users can create posts and add comments.

One small but important thing: suppose that we want to enumerate comments under the post so that they have a stable identification: sequential numbers starting from 1 in each post. For example, if there are comments #1, #2 and #3, and the second comment is deleted, the third comment still needs to have number 3.

Logical model

Here is a bare minimum logical model that covers those requirements. Also, we use the baseline table design strategy (with surrogate primary keys). There is nothing surprising in this section; it’s all very basic.

Anchors:

Attributes:

(For brevity, we do not bother with specifying attributes of User.)

Links:

Summary as a link diagram:

Baseline table schema

Table schema as SQL:

CREATE TABLE users (  
  id INTEGER NOT NULL PRIMARY KEY,  
  . . .  
);

CREATE TABLE posts (  
  id INTEGER NOT NULL PRIMARY KEY,  
  user_id INTEGER NOT NULL,  
  body VARCHAR(1500) NOT NULL  
);

CREATE TABLE comments (  
  id INTEGER NOT NULL PRIMARY KEY,  
  post_id INTEGER NOT NULL,  
  seq_num INTEGER NOT NULL,  
  user_id INTEGER NOT NULL,  
  body VARCHAR(1500) NOT NULL  
);

NB: comments table definition may be incomplete, and will be the focus of our discussion.

Here are the foreign keys in this schema:

• posts.user_id references users.id;
• comments.post_id references posts.id;
• comments.user_id references users.id;

Composite primary keys

Let’s take a closer look at the Comment anchor, and the corresponding database table.

Suppose that we have two posts, ID=100 and ID=101. The first one has two comments, the second one has three comments. Let’s look at the data in the “comments” table.

The id column is a primary key, so it’s by definition unique.

Let’s make some observations.

First, if we look at the combination of “post_id” and “seq_num”, we can see that they are also supposed to be unique. We sort of implicitly specified that in the business requirements, but it only becomes clear when we look at the data.

To achieve this, we would just add a UNIQUE constraint:

ALTER TABLE comments ADD UNIQUE (post_id, seq_num);

Second observation: we now have two different unique identifiers: “id” and a combination of “post_id” and “seq_num”. The former contains meaningless numbers; could we get rid of it?

Yes, we could use a composite primary key for our anchor table:

CREATE TABLE comments (  
  post_id INTEGER NOT NULL,  
  seq_num INTEGER NOT NULL,  
  PRIMARY KEY (post_id, seq_num),  
  user_id INTEGER NOT NULL,  
  body VARCHAR(1500) NOT NULL  
);

Foreign keys do not change:

• comments.post_id references posts.id;
• comments.user_id references users.id;

To accommodate our composite primary key, we need to update the definition of our Comment anchor:

We see that the ID example is now a tuple (a pair of two integers in parens); same for the ID storage column.

We need to carefully distinguish several things:

• anchor ID of the Comment anchor: (101, 3);
• where this anchor ID is stored: two table columns, “post_id” and “seq_num”;
• the fact that those two columns belong to the primary key;

Also, we need to carefully understand the causality here:

• We wanted to use a composite primary key because we wanted to get rid of the “id” column;
• We can no longer use the surrogate anchor ID for the Comment anchor (such as 10031);
• This forced us to change the anchor ID of the Comment anchor to use a combination of Post ID and the number attribute, such as (101, 3);
• This combination also became a primary key of our “comments” table, just as we wanted.

Consequences of composite primary keys

So, what happens if we use a composite primary key in our anchor table? Let’s start with the desirable consequences: basically, why do we even bother?

Primary key index performance

Let’s look at a typical query that we expect to execute a lot:

SELECT *  
FROM comments  
WHERE post_id = ?  
ORDER BY seq_num

In the baseline table schema this query would be served by the index that underlies the “UNIQUE (post_id, seq_num)” constraint.

With the composite PK schema it would be served by the index that underlies the primary key.

Moreover, some important databases, such as MySQL or SQL Server, support index-organized tables, also called clustered indexes. This means that the rows in our table would physically be stored next to each other, so that our query may be super efficient. The efficiency comes from the fact that a single read of a disk page will often retrieve all the comments for a post.

Storage size

Sometimes it makes sense to keep track of how wide the table is. Our current example is not very illustrative here because it contains a VARCHAR column, so let’s consider a different use case.

Suppose that we have restaurant orders, with each order containing several order items. Here is a baseline version of “order_items” table:

CREATE TABLE order_items (  
  id INTEGER NOT NULL PRIMARY KEY,  
  order_id INTEGER NOT NULL,  
  position INTEGER NOT NULL,  
  UNIQUE (order_id, position),  
  quantity INTEGER NOT NULL,  
  item_id INTEGER NOT NULL  
);

It has five columns: id, order_id, position, quantity, and item_id. Each one has integer type; that is, the total size of a table row would be 5 * 4 bytes = 20 bytes.

We can get rid of the “id” column and instead use a composite primary key of “order_id” and “position”:

CREATE TABLE order_items (  
  order_id INTEGER NOT NULL,  
  position INTEGER NOT NULL,  
  PRIMARY KEY (order_id, position),  
  quantity INTEGER NOT NULL,  
  item_id INTEGER NOT NULL  
);

Here we have four columns, and the total size of a row would be 4 * 4 bytes = 16 bytes.

That means that we can store ~205 rows in a hypothetical 4096-byte page in the baseline schema (4096 / 20).

In the composite PK schema we would have 4096 / 16 = 256 rows. That is, a single page read brings ~25% more records to be processed by the query engine, and cached by the memory subsystem.

Those calculations are very approximate, and the behaviour may vary wildly depending on the database server and data distribution. You need to run a proper experiment to see if it’s possible to extract performance wins in your specific configuration, using this method.

Nevertheless, for many database servers and table schemas some queries may actually see performance improvements from reducing the physical size of a table row.

Anchor IDs and foreign keys

Let’s add a feature: voting for comments. It is implemented by the following link:

In the baseline table strategy this table looks like this:

CREATE TABLE comment_votes (  
  user_id INTEGER NOT NULL,  
  comment_id INTEGER NOT NULL,  
  PRIMARY KEY (user_id, comment_id)  
);

CREATE INDEX ON comment_votes (comment_id);

It’s a classic two-column junction table:

• comment_votes.user_id is a foreign key to the users.id column;
• comment_votes.comment_id is a foreign key to the comments.id column.

What if our “comments” table uses a composite primary key? Here is how “comment_votes” would look:

CREATE TABLE comment_votes (  
  user_id INTEGER NOT NULL,  
  comment_post_id INTEGER NOT NULL,  
  comment_seq_num INTEGER NOT NULL,  
  PRIMARY KEY (user_id, comment_post_id, comment_seq_num)  
);

CREATE INDEX ON comment_votes (comment_post_id, comment_seq_num);

Now our junction table has three columns:

• user_id is again a foreign key to the users.id column;
• a pair of (comment_post_id, comment_seq_num) is a foreign key to (comments.post_id, comments.seq_num) columns.

If we had a composite User anchor ID (say, composed of the Company ID and a User ID within the company), we’d have four columns in our junction table.

So, one consequence is that our anchor ID becomes “viral”, so to speak. In every other table that refers to comments we need to use the same pair of columns as foreign keys: comment reactions, flagging comments, etc.

NB: Note that the row size calculations from the previous section apply here. For the baseline table strategy, the row size is 8 bytes (two columns). For the composite Comment ID it’s 12 bytes.

Using a page size of 4096 bytes, one page stores ~512 table rows versus ~341 in the composite case: 33% fewer records per page. This may have performance consequences in some cases.

When are composite anchor IDs possible

A composite anchor ID involves the following:

• a parent anchor (e.g. Post);
• a child anchor (e.g. Comment); its table would get the composite PK;
• a 1:N link between them;
• an attribute on the child anchor (e.g. user-visible number of the Comment);

There are four criteria:

• The parent anchor is required: a child anchor makes no sense without a parent anchor.
• The attribute is always present, and its value is unique among all child anchor instances within the parent anchor instance;
• The child anchor cannot move into a different parent anchor.
• The attribute value is immutable.

Let’s look again at the fragment of the link diagram for our example:

A composite anchor ID requires two anchors, a 1:N link between them, and an attribute on the child anchor. This is a basic structural requirement.

Let’s check if the criteria are met:

• dangling comments do not make sense, according to our business requirements;
• every comment gets a number when it’s created, so it is always defined;
• “reassigning” a comment to a different post makes no sense, according to our business requirements;
• our business requirements specifically asked for the comment number to stay stable;

Why the “parent anchor is required” requirement? Because anchor IDs must be fully defined. The corresponding primary key also must be NOT NULL, in SQL terms.

The same applies to the “attribute always present” requirement. Anchor IDs must be fully defined and be unique.

Why the “child anchor cannot move into a different parent” requirement? Because anchor IDs should be immutable. Technically speaking, it’s possible to change an anchor ID, but this is such a low-level operation that we really don’t want to go there. Thus, anchor IDs should be immutable, and child anchors cannot move into a different parent.

Note that our examples say: “according to our business requirements”. This is important, because business requirements can change and invalidate the possibility of a composite anchor primary key.

In the posts/comments case it’s quite clear, but sometimes you may find out later that your initial assumption was wrong. Also, your business requirements may change so that your parent anchor becomes optional. This will put you in an awkward position, because anchor IDs are fundamental. You will have to do a database migration, or invent a special value for the missing parent anchor, e.g. zero (such values are called “sentinel values”).

Concerns discussed in this section do not exist for the baseline table design strategy. In the baseline strategy, every anchor has its own set of anchor ID values, independent from any other anchor and links. When a link is changed, you only need to migrate the columns involved, and nothing else.

When are composite anchor IDs not possible

Here is another use case that looks very similar to the posts/comments scenario, but with one important difference that may prevent us from using composite PKs.

Consider an issue tracking system, similar to the well-known Jira. There are projects, and you can create issues inside the projects.

Projects have external IDs that look like “FOO” (for the Foo project). Projects also have numeric anchor IDs (invisible to the user).

Issues have an externally visible number that is unique inside the project, for example “FOO-123”.

It looks like this is basically the same example as posts/comments:

• issues must have a parent project;
• issue number is required, and unique within the project;
• issue numbers seem to be immutable, since we explicitly want them to be stable.

But one requirement cannot be satisfied: in the real-world Jira, you can move issues between projects. When they move, they may also get a different number. So, for example “FOO-123” can become “BAR-20”.

The original identifier is preserved and redirects to the current identifier.

If we were to build a Jira clone, this would prevent us from using a combination of Project ID and Issue number as a composite anchor ID on the “issues” table. All because anchor IDs must remain stable.

Note also that this demonstrates another potential problem with business requirements evolution. Imagine that we are a startup building an issue tracker some time in the past, when issue trackers were an innovative piece of software. It’s possible that initially we might not realize that we need to move issues between projects.

So, we implement a simpler version that actually lets us use composite anchor ID, and we use our favorite composite PK on the “issues” table.

A few years later our customers started nagging us, requesting a feature for moving issues between projects. We can’t help but see that this is actually a very useful feature, but by then we’ve already committed deeply to this schema. Every piece of functionality related to issues depends on the composite PK:

• comments;
• linked issues;
• assignments;
• custom fields;
• etc., etc.

Well, migration is always possible, but it won’t be trivial.

Physical implementation should follow business requirements

The issue tracker example demonstrates how fragile this set of requirements is.

But the problem is even more fundamental. Normally, a physical implementation must follow from the business requirements.

Let’s look at the causality of this implementation.

First, our business requirements (“moving issues between projects is not needed”) allowed us to use composite anchor IDs.

Second, when we decided to use them, and to use composite primary keys, the business requirements got encoded in a rigid physical structure (the primary key).

Finally, when the product owner asks us to implement moving issues between projects, we are in a difficult situation, because primary keys basically say “moving issues between projects is not possible”.

I mean, technically, if you think about it, you can implement some workaround that would involve a lot of carefully constructed UPDATE statements, but this is a source of implementation risk.

This pattern is a very illustrative example of how a physical implementation may constrain your business requirements: it actually should be the other way around!

Conclusion

So, why do we bother? First, you need to understand this pattern even if you never use it yourself. You will sometimes find it in legacy databases, in older textbooks, and in the collective body of knowledge of relational database design.

Decades ago there was a school of thought in relational modeling that abhorred surrogate keys (i.e. numbers without a specific business meaning). If you had a unique combination of columns, you were supposed to use it. If a combination of Post ID and comment number is unique, then you are not supposed to introduce a separate Comment ID.

Conceptually, this was probably the main driving force behind composite PKs in anchor (entity) tables. This school of thought has lost out completely: generally people do not bother and just use simple numeric IDs for everything.

The goal of this text is to demonstrate the consequences of using composite PKs. Primary consequences are at the physical level:

• for some databases, composite PKs may help with typical queries, due to the use of clustered indexes;
• for some tables, composite PKs improve physical table density (number of table rows per disk page); for some, though, the effect would be the opposite;

Physical-level characteristics, though, greatly depend on a specific database server implementation.

Also, we uncover some conceptual weaknesses:

• composite PKs are “viral”: they dictate the physical schema of related tables;
• when we encode a business requirement in a composite PK, this requirement becomes hard to change or remove, because:
• when composite PKs need to change, the physical schema of ALL related tables needs to change;
• so, when business requirements change and a composite PK becomes ineligible, you need to either do a database migration or invent sentinel values;
• it’s virtually always possible to just NOT use composite PKs: usual numeric IDs are always an option;

In part 4, we’ll discuss an interesting aspect of this: it turns out that we can decouple primary keys of anchor tables from anchor IDs.

“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.

Purchase for €32

I’d be happy to hear your feedback and questions:
Alexey Makhotkin squadette@gmail.com.