Primary keys, foreign keys, and relationships

Here is a database-design puzzle you can solve with common sense.

The orders table needs to record which customer placed each order, so should every order row contain the customer’s full name, email address, and phone number?

At first, that might seem convenient:

But Erik’s information would be copied into every order he places.

If he changed his email address, the application might need to update hundreds of order rows, and if one row were missed, the database would contain conflicting answers about Erik’s current email. So which answer is right?

The relational model offers a better approach: store an important fact in one authoritative place, and connect other records to it using identifiers.

Those identifiers are called keys.

What is a primary key?

A primary keyConcept · lights on your mapprimary keyA column, or set of columns, whose value uniquely identifies each row in a table. It is the row’s unique identifier inside its table, and two rows cannot share the same primary-key value. Names and email addresses are usually poor primary keys because they can change and may not be unique. Erik can change his email while remaining customer 88. uniquely identifies each row in a table.

Consider the customers table:

The customer_id column is the primary key.

Its values, 88 and 89, must uniquely identify the rows, and two customers cannot both have the same primary-key value within this table.

A useful mental model is: the primary key is the row’s unique identifier inside its table.

Names and email addresses are usually poor primary keys because they can change, and they may not always be unique. Erik can change his email while remaining customer 88.

Primary keys in different tables

Each table usually has its own kind of identifier.

The customers table may use customer_id, the orders table order_id, and the products table product_id.

For example:

Here, order_id is the primary key of the orders table.

Order 1042 and order 1043 are separate rows, even though they belong to the same customer.

Primary keys are not always simple numbers

Numeric IDs are common because they are compact and efficient, but primary keys can take other forms. A system might use an identifier resembling ord_7M4K92 or 550e8400-e29b-41d4-a716-446655440000, and some tables use a combination of columns as a key. This is called a composite key.

You do not need to choose among these designs yet. The important principle is that the primary key uniquely identifies one row.

API identifiers and database keys

In Module 6, you saw API paths such as: GET /customers/88

The identifier 88 may correspond directly to the customer’s primary key in the database.

However, this is not guaranteed.

A company may use an internal database ID while exposing a different public identifier through its API, which allows the database design to change without necessarily changing the API contract.

The broader connection is: API resource identifiers often help the backend locate particular database records.

What is a foreign key?

Now return to the orders table.

Instead of copying Erik’s name and email into every order, each order can store his customer ID:

In this table, customer_id is a foreign keyConcept · lights on your mapforeign keyA column, or group of columns, that refers to a key in another table. The orders table’s customer_id value of 88 means “this order belongs to the customer whose primary key is 88.” The order stores the identifier needed to find the customer record, not another copy of the customer’s details..

A foreign key is a column, or group of columns, that refers to a key in another table.

Here, orders.customer_id refers to customers.customer_id.

The value 88 means: this order belongs to the customer whose primary key is 88.

Because the order stores the identifier needed to find Erik’s customer record, it does not need another copy of his current email.

Follow the reference

The two tables can be read together.

Customers

Orders

To answer “Who placed order 1042?”, follow the relationship: orders.customer_id = 88

Then find: customers.customer_id = 88

The matching customer is Erik.

A useful mental model is: a primary key identifies a row. A foreign key connects another row to it.

Foreign-key constraints

A database can do more than store the number 88. It can enforce a foreign-key constraint.

The constraint can require every orders.customer_id value to match a real customer.

If the application attempted to create this order:

but customer 9999 did not exist, the database could reject the record.

This protects referential integrity, meaning that references between tables remain valid. Without the constraint, the database could contain an order pointing to a customer who does not exist. The constraint closes that door.

Database teams sometimes choose not to enforce every relationship directly through foreign-key constraints, especially in certain large or distributed systems, but the conceptual relationship still exists and must be protected somewhere.

One-to-many relationships

The relationshipConcept · lights on your maprelationshipThe connection a foreign key creates between records in different tables. Common shapes: one-to-many (one customer has many orders; each order belongs to one customer), one-to-one, and many-to-many (represented with a connecting table). Relationships are why the database is called relational; it models how the parts of the business connect. between customers and orders is usually one-to-many.

One customer can place many orders:

Each individual order belongs to one customer.

This can be described as: one customer has many orders. Each order belongs to one customer.

The foreign key is stored on the “many” side: orders.customer_id

Many order rows can contain the same customer ID.

One-to-one relationships

Some relationships are one-to-one.

For example, suppose each employee has one security profile, and each security profile belongs to one employee. The system might store an employees table and a security_profiles table, with a relationship connecting one row in each table.

One-to-one relationships are less common than one-to-many relationships, but they can be useful when information has different security, ownership, or organizational requirements.

Many-to-many relationships

Some relationships are many-to-many.

An order can contain many products, and one product can appear in many different orders, so neither table can represent that relationship using only one foreign-key column.

Instead, the database introduces a connecting table, sometimes called a join table or junction table.

Orders

Products

Order items

The order_items table connects orders and products.

Each row says: this product appeared in this order in this quantity.

The table can contain foreign keys pointing to both:

This turns a many-to-many relationship into two one-to-many relationships.

What is a data model?

A data model describes the important entities in a system, the information stored about them, and the relationships connecting them.

For the coffee application, the model might include:

• Customers
• Orders
• Order items
• Products
• Stores
• Payments
• Addresses

The model could express relationships such as:

This is more than a technical diagram.

The data model is one way the product represents the real-world business it serves.

The data model affects product capabilities

Imagine that the coffee application originally stores one address directly on each customer:

Later, the product team asks: “Can customers save several delivery addresses?”

The current structure assumes that one customer has one address.

Supporting multiple addresses may require a separate table:

Now the system can represent: one customer has many addresses.

This may affect more than the database. The team may also need to update:

• Backend logic
• API contracts
• Frontend screens
• Validation
• Existing customer data
• Internal support tools
• Tests

This is why a request that sounds visually small can require meaningful structural work.

Store facts once—but understand the exceptions

A common relational-design principle is to avoid unnecessarily storing the same fact in several places.

Erik’s current email should usually have one authoritative home in the customers table, which reduces the risk of contradictory copies and reflects the source-of-truth principle from Module 1.

However, “store each fact once” is not an absolute law.

Sometimes a system intentionally records a historical snapshot.

For example, an order may store the delivery address used at the time of purchase. If Erik later moves, the company should not rewrite the address on an order that was delivered last year.

Similarly, an order item may store the price charged at checkout even if the product’s current price later changes.

The product may need both:

• Erik’s current address in the customer or address table
• The historical delivery address used for a particular order

These values may look similar, but they represent different facts.

Good data modeling asks: is this an unnecessary copy, or is it an intentional record of what was true at that moment?

What happens when copies disagree?

Uncontrolled duplication can create conflicting data.

Imagine that Erik’s email appears in:

• The customer table
• Every order
• Every payment
• Every support case

If his email changes and only some records are updated, the system no longer has one clear answer. It has several.

These inconsistencies are called data anomalies.

Relational database design often uses a process called normalization to reduce unnecessary duplication and make each fact’s authoritative home clearer.

You will not need to master normalization rules yet. For now, remember the goal: organize related facts so that updates do not create avoidable contradictions.

Seeing the complete relationship

Suppose Erik places an order containing two lattes and one croissant.

The database may store:

One customer

One order

Two order-item rows

Two products

The keys connect the records:

The database stores related facts in appropriate tables and connects them using keys, which is why it does not need to copy every detail into one enormous row.

The mental model to remember

A primary key uniquely identifies a row within a table.

A foreign key refers to a key in another table and creates a relationship between records.

A foreign-key constraint can ensure that the referenced row actually exists.

A one-to-many relationship means one record can relate to many records in another table.

A many-to-many relationship is commonly represented using a connecting table containing foreign keys to both sides.

A data model describes the entities, attributes, and relationships that represent a product’s world.

Relational design tries to give important facts clear authoritative homes while avoiding unnecessary copies that can disagree.

You should now understand why the word relational matters. The database does not merely store separate lists. It models how customers, orders, products, payments, and other parts of the business connect.