ID design and primary keys, pt. 1

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

This is the first part of a systematic discussion of primary keys in database design. As usual, we present the material in a way that deviates from the traditional approach.

This is basically a bonus chapter from the “Database Design Book”. The goal of this text is to teach you how to design your primary keys based on business requirements.

In part 1, we begin at the logical level.

• We introduce the idea of external IDs.
• We discuss anchor IDs and their requirements; particularly, when anchor IDs can be used as external IDs.
• Then we talk about handling external IDs generated by outside systems; particularly, which external IDs could be used as anchor IDs;

Then we move to the physical level.

• We discuss the idea of a primary key, fully dissociated from its business meaning;
• Then we discuss a simple use case: a tiny content management system that demonstrates a simple anchor table design with integer primary key;
• Additionally, we talk about uniqueness constraints and their relationship with external IDs;

Table of contents

In part 2, we’ll discuss composite primary keys and how they are used in database design.

Subscribe here to receive updates:

External IDs

Let’s forget for a moment about databases, tables, primary keys and other things that exist on a physical level.

We need to focus first on business requirements, and on the logical model that could be extracted from them.

In many business-oriented systems some entities need to have a unique identifier. Some examples:

• spare parts may have one or more part numbers;
• people have a unique taxpayer identification number, such as SSN in the United States or BSN in the Netherlands;
• pages in a content management system can have a URL such as /about, or just /content.php?id=25;
• ticket-tracking systems use familiar strings such as FOOBAR-123;
• etc., etc.

Let’s call such unique identifiers external IDs. They could be used externally: sent in an email, printed on a piece of paper, told over the phone. External IDs have three defining properties:

1. External IDs uniquely identify an entity: there is exactly one entity corresponding to each external ID.

2. The opposite may not always be true: a single entity may have no external ID, one external ID, or more. For example, many children do not have a passport. Passports also can be reissued, but we can identify a person by their old passport number.

3. External IDs can change. Alright, we need to make the first property more precise: “at any given moment there is exactly one entity corresponding to each external ID“. For example, you may want to rename your social media handle, and somebody else can grab your old one. So, user @alice today may be a different Alice later.

There could be more than one type of external ID for an entity. For example, if we sell spare parts on Amazon, they would have both part numbers (assigned by vendor) and ASIN (assigned by Amazon).

Anchor IDs

Now we can remember again that we have a database, but it’s still a bit too early to talk about tables and primary keys.

In the “Database Design Book” we use the term “anchors”. Anchors are mostly like entities, but we don’t like the word “entity” because it is too ambiguous.

Anchor ID is required for reliable and unambiguous identification of anchor instances.

Suppose that we maintain a database of books, and there are 100 titles in our database. We need a way to identify each of the 100 books in such a way that every book has an anchor ID, and every anchor ID corresponds to exactly one book.

We cannot use ISBN, because some books do not have ISBN. We cannot use the title: maybe we have five different Bibles in our collection, and so on.

A common solution for this problem is to use integer numbers, starting from 1, 2, 3, and so on. So we’d have a book with ID=1, a book with ID=2, and so on. We could use such integer numbers in the actual database tables. They do not have their own meaning.

An additional requirement for the anchor ID is that it is immutable: its value never changes. Meaningless integer numbers satisfy this requirement because you just never need to change them: ID=2 is no better or worse than ID=3.

Simple integer numbers are the most common solution, but sometimes we have other options:

• unique strings such as “fr” or “CHF”;
• tuples: a combination of two or more integers or strings;
• UUIDs, though we can treat them as just big non-sequential integers;

We’ll discuss such scenarios later in this series of posts.

Anchor IDs as external IDs

Any anchor ID could in principle be used as an external ID, and this often does happen.

Sometimes, however, this is undesirable. Consider an e-commerce system, with users placing orders. Each order has an order ID. Most certainly, we have an “orders” table that has an “orders.id” column, that contains auto-incremented integer IDs. Can we use those numbers in confirmation emails etc.?

Technically we can, but this creates a possibility of industrial espionage. Our competitors can analyze how quickly the sequential number grows, by periodically making an order. This allows them to track your business results, and you may not want this.

To circumvent this, you can generate date-based + random IDs such as “20261016-32767”, and use them externally. They would be stored as an attribute of the Order anchor, but would only be used between you and the customer to refer to an order.

Everywhere else in the database you would use the meaningless integer number because it is often the most convenient technically. (We’ll talk about when this may not be the case later.)

Note that our system needs to validate and authenticate those external IDs even though they were generated by our system. For example, if somebody submits a request to cancel a reservation QIE3CB, we need to make sure they have the authority to do that. Maybe they just eavesdropped on somebody else’s reservation number.

External IDs from outside systems

Some external IDs are generated by our own system. We know their meaning and we trust them. We just need to validate and authenticate them.

But there are also external IDs generated by outside systems. There are lots of potential issues with them.

First, something that looks like an external ID may not even be a proper external ID. For example, two people living in two different countries may happen to have the same passport number. Thus, a passport number by itself may not be a good external ID at all, because by definition we want each ID to correspond to only one entity.

An ID can also be forged, as mentioned above.

In many cases you may decide that this is not an external ID but just an attribute value of a different anchor. For example, suppose that you build an airline reservation system. You ask the customer to enter their passport number — how reliable is it? Maybe you just need to make this an attribute of your Reservation anchor: “What is the passport number supplied by the customer for this Reservation?” You won’t even have a separate anchor for passports, you just have attribute values.

External IDs as anchor IDs

Suppose that we showed that an identifier satisfies the requirements described above. Or, it is generated by our own system, so we can trust it after authentication and validation.

However, external IDs often do not pass the anchor ID requirements:

• sometimes more than one external ID can refer to an entity;
• sometimes an entity does not have an external ID;
• sometimes a value of an external ID can be changed;

In some cases, though, these additional requirements are also satisfied, and we finally have a proper external ID that could be used as an anchor ID. No meaningless numbers needed, right?

Later we’ll discuss some use cases where this is possible. Also, we need to discuss why you would want to do that.

Primary keys

Now let’s forget for a moment about business requirements and logical models, and go all-in into the physical level, where the primary keys live.

Imagine a physical table in a relational database. Let’s scramble the table name and the column names so that we can talk about the essence of primary keys.

Here is some example data in this table:

Here is the definition of this table, with column names, data types, primary key definition, and a uniqueness constraint:

CREATE TABLE prawnges (  
  iro INTEGER NOT NULL PRIMARY KEY,  
  stoog VARCHAR(64) NULL,  
  qonts TEXT NOT NULL,  
  UNIQUE (stoog)  
);

A primary key consists of one or more columns and uniquely identifies every row of a table. Here, iro=5 corresponds to the first data row; iro=27 corresponds to the second data row, and so on.

You cannot put a NULL value into the iro column, you need a definite integer value. Also, you cannot add another row with, say, iro=5, again: the database will reject this with the “Primary key violation” error.

Again, in this example we’ve used a single-column primary key, but they can also be composite. We could add a non-NULL column called “b” and declare the following primary key: (iro, b). The combination of values from both columns then needs to be unique: (5, 10), (5, 5), (10, 10), and so on.

We’ll discuss composite keys in more detail in the second part of this series.

Simple anchor tables

Imagine a minimal content management system. It supports web pages, where each page can have a readable URL such as /about, or just /content.php?id=25.

Here is a logical model of this system, using the notation introduced in “Database Design Book”. It has just one anchor:

And two attributes:

We used the baseline table design strategy:

• the anchor gets its own table;
• attributes get their own columns;
• we use 1, 2, 3, … as anchor IDs;
• the anchor ID gets its own column which is also a primary key;
• the “slug” attribute is an external ID, so it gets the uniqueness constraint;

A lot of words for this little table, isn’t it:

CREATE TABLE pages (  
  id INTEGER NOT NULL PRIMARY KEY,  
  slug VARCHAR(64) NULL,  
  content TEXT NOT NULL,  
  UNIQUE (slug)  
);

Wait, doesn’t it look familiar? Let’s look at the sample dataset:

Okay, this is definitely the unscrambled version of “prawnges” from the previous section.

Uniqueness constraints

Virtually all databases support uniqueness constraints. When you design a table schema, you can define a uniqueness constraint on a column in that table. It means that the values in that column need to be unique. If you try to insert a new row with a duplicate value in such a column, you will get a uniqueness constraint violation error from the database. Same for changing values in the existing rows.

Primary keys include an implicit uniqueness constraint. That’s why you will never get duplicate primary keys in a table.

There could be several uniqueness constraints on a single table. Also, a uniqueness constraint can cover more than one column, same as composite primary key. Uniqueness constraints cannot be defined across two or more tables.

There is a uniqueness constraint in our “pages” table that we discussed.

Let’s look at the definition of the “slug” column (lines #3 and #5):

CREATE TABLE pages (  
  id INTEGER NOT NULL PRIMARY KEY,  
  slug VARCHAR(64) NULL,            -- #3  
  content TEXT NOT NULL,  
  UNIQUE (slug)                     -- #5  
);

We see that this column is nullable, and it’s defined as UNIQUE. In most modern databases you can define a nullable column as unique. Here is how it works for the nullable column:

• if the value is not NULL then this value must be unique among other non-NULL values;
• otherwise, more than one row can contain a NULL value.

> Historically, the interplay between NULLs and uniqueness was somewhat complicated, without a very good reason. We’ll discuss this in more detail in the “Nitpicking” part.

The page slug attribute was defined as external ID. Note that this was our business decision: only we know that slugs are unique.

On the physical level, external IDs are implemented by uniqueness constraints: directly, or implicitly via primary key.

“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

Conclusion

External IDs are particularly important in database design. They exist fully on the logical level, but they are somewhat more closely tied to physical table design, closer than ordinary attributes.

Anchor IDs live somewhere between logical and physical levels, and they are essential for table design. In most cases they can just use the most common approach: simple integers. In the third part we’ll discuss some interesting alternative options that you have.

Anchor IDs can often be used directly as external IDs generated by your system. However, in many important cases we need separate external IDs.

Primary keys are required for any table. We’ve discussed the most common simple case: an anchor table with a simple integer primary key.

Uniqueness constraints are closely associated with external IDs on the logical level. On the physical level, every primary key has an associated uniqueness constraint.

In part 2 we’ll discuss composite primary keys and how they are used to implement the most common table design strategies, namely:

• link tables;
• Entity-Attribute-Value (EAV) tables;
• ad-hoc secondary data;
• anchor tables with composite PKs;
• ad-hoc cases for special purposes;

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