The Keys to Designing Databases for What We Don't Yet Know

I have been managing databases for a while now, and I can never emphasize enough how important it is to design your database with an open mind. Don't make assumptions on your users, and don't be a lazy developer. So I put together some key points that I want you to think about as you are just getting started. Oh, yah, also please get to know your data.

Let start with the basics: a relational database is a dataset of tables that are interconnected by common keys (column values). And these tables can be stored in csv files and have an associated schema that defines how the tables are connected.

For example, here is the schema for the NGA-Subduction Database, which I store in csv files:

How do we design a database schema that can evolve and adapt without sacrificing data integrity?

When designing a database, we make decisions based on what we know about the data today. We define tables, establish relationships, and assign keys that uniquely identify records. But what happens when our understanding of the data changes, new information becomes available, or the database needs to support applications we never anticipated?

A database schema should not simply accommodate the data we have today. It should be designed to evolve, while preserving the integrity, meaning, and traceability of the information it contains.

This is particularly important for scientific and engineering databases, where the consequences of duplicated, misidentified, or incorrectly associated data can extend well beyond the database itself, affecting analyses, statistics, and ultimately engineering decisions.

What makes a record unique?

Let's start with something seemingly straightforward: the primary key.

A primary key uniquely identifies each record in a database table. It cannot contain null values, and no two records can share the same primary key. But a primary key does not have to be a single column. It can consist of a combination of columns, known as a composite primary key.

Consider a ground-motion database, where each recording is associated with an earthquake event and the station that recorded it. We might have three tables: one for earthquake events, one for recording stations, and one for the ground-motion recordings themselves.

The Events table has an EventID as its primary key. The Stations table has a StationID. In the Recordings table, EventID and StationID are foreign keys, referencing their respective tables and establishing the relationships between the records.

If our database contains exactly one recording per station-event combination, we could use (StationID, EventID) as a composite primary key. Neither StationID nor EventID is individually unique in the Recordings table, but their combination is.

This gives us something more valuable than an arbitrary identifier: it expresses what makes each recording unique under our definition of the data.

A unique identifier doesn't necessarily mean unique data

An alternative approach is to assign each recording a unique Record Sequence Number (RSN), which becomes its primary key. This is convenient because every recording can be referenced using a single number.

But consider what happens if we accidentally enter the same station-event combination twice, assigning a different RSN to each entry.

From the database's perspective, there is no problem. Both records have unique primary keys. Yet, under our original definition of a recording, we have duplicated an observation.

We have made every row uniquely identifiable without ensuring that every row represents a unique observation.

This is one reason I find composite keys particularly valuable. They can express meaningful relationships in the data while providing a built-in mechanism for preventing duplicates.

Of course, we don't have to choose between the two approaches. We can retain RSN as the primary key and add a uniqueness constraint on (StationID, EventID). This provides the convenience of a single identifier while enforcing the underlying definition of uniqueness.

These are two different concepts: the primary key identifies the row, while a uniqueness constraint can enforce additional rules about the data. When multiple minimal sets of columns can uniquely identify a record, they are called candidate keys. One is selected as the primary key, and the others can be retained as alternate keys.

The important point is that assigning a unique identifier should never be mistaken for performing quality assurance.

But what happens when the database evolves?

Now consider how our ground-motion database might change over time.

Suppose we originally assumed that each station-event combination corresponds to a single recording. Later, we want to include multiple instruments at a station, different recording components, or multiple processing versions of the same original observation. Perhaps we also want to combine recordings from different databases, each with its own identifiers and conventions.

Suddenly, our original definition of uniqueness may no longer be sufficient.

We could expand the composite key to include additional columns, such as an instrument identifier. But how far should we go? Should we keep adding columns to the primary key every time the database evolves?

Alternatively, we could rely entirely on an assigned unique ID. This would make it easier to add new records, but it would not resolve the more fundamental question of what makes those records distinct.

I think the better approach begins with understanding the relationships between the data.

An earthquake event exists independently of the stations that record it. A station exists independently of the events it records. A recording represents an observation associated with both. A processed version of that recording is something different: it is derived from the original observation.

If we represent these distinctions explicitly, we can introduce new instruments, components, processing histories, and sources without necessarily redefining the existing events, stations, or observations.

This is where thoughtful database design becomes much more important than simply choosing a primary key.

Quality assurance versus efficiency

I kind of like using compound keys so I know exactly what data I am dealing with, but there are legitimate reasons to use single-column identifiers. They can simplify relationships between tables, reduce the complexity of queries, and, depending on the database structure and indexing strategy, improve performance. In my case I chose to use a single primary key (NGASubrRSN) because I was dealing with many tables, so connecting many pairs would get tedious.

But I would be careful about treating these advantages as automatically more important than meaningful data constraints.

To me, quality assurance is more important than an apparent gain in efficiency.

A database that retrieves incorrect or duplicated information very quickly is not a successful database.

That doesn't mean we should always choose composite primary keys. In fact, a single-column primary key combined with appropriate foreign keys and composite uniqueness constraints may be the best design for many applications.

What matters is that the decision is intentional. We should not sacrifice the rules that protect the data merely to simplify its implementation.

And database constraints are only one part of quality assurance. We must also consider consistent identifiers, data provenance, validation, and the possibility that the same real-world observation may enter the database through different sources or under different names.

Designing for change without anticipating everything

There is an obvious dilemma here. We want a database that can accommodate future developments, but we cannot predict every possible change.

Trying to anticipate everything can lead to an unnecessarily complicated schema, with relationships and abstractions that may never be needed. On the other hand, designing exclusively around current requirements can make even modest future expansions difficult.

I think the key is to distinguish between what is fundamental to the data and what reflects our current use of it.

We should define entities and relationships based on their meaning, establish constraints that enforce what we currently know to be true, and keep separate concepts that may evolve independently. When our understanding changes, we should be able to extend or revise the schema deliberately, without silently changing the meaning of existing records.

That includes maintaining explicit migration procedures, preserving historical identifiers where appropriate, and recording enough information to reproduce earlier analyses.

The objective is not to design a database that will never need to change. It is to design one in which changes can be made without losing the information, relationships, and quality assurance that made the original database trustworthy.

The bigger picture

A database schema is more than an arrangement of tables and columns. It represents our understanding of the information we are collecting and the relationships we believe exist within it.

Primary keys, foreign keys, composite keys, and unique identifiers are tools for expressing and enforcing that understanding. They are not substitutes for it.

As databases grow, particularly scientific databases that may remain in use for decades, their structure needs to accommodate new knowledge without compromising existing information.

The real challenge is not making every record unique. It is understanding what makes the data unique, preserving that meaning, and designing a structure that can evolve as our understanding changes.