Member-only story
Data Model Design 101: Composite vs Surrogate Keys
When to know which type of key to use in your data models
I’ve recently been writing a data model to represent a new part of our business. The data requires a lot of questions to be asked, as it’s quite difficult to understand intuitively.
The data model requires that I join similar, yet different, datasets from two different sources into one dataset. Any time you merge two datasets, it’s integral for you to think about the unique key that will then act as the primary key of this new dataset.
Unfortunately, you can’t assume that the primary key in each dataset will carry over into the resultant one. This is because these keys, if incrementing integers, will tend to be duplicated from dataset to dataset.
However, you can create a new key.
In this article, we will discuss two options for creating a unique key in a data model—a surrogate key or a composite key. What are the differences between these? When should you use one versus the other?
Surrogate vs Composite Key
Composite keys are made up of more than one identifying field, together the fields that make it up are unique. They are created from real-world values and whose meaning can be understood…
