# Common Sense Design

Design tables from the questions the data must answer. Learn from Northwind's design - one subject per table, columns at their smallest useful size, a primary key in every table, and foreign keys to link them.

Journeyman · Database · SQL · Updated November 3, 2026

## After this lesson you can

- Plan tables from what the data is for, one subject per table
- Explain granularity, primary keys and foreign keys in a table design

## Key takeaways

- Plan tables from what the data is for: the screens, reports and common questions it must answer.
- Each table is about one kind of thing, and each row is one of those things.
- Store each value in its smallest useful piece, such as `City` apart from `Address`, so you can filter and sort on it.
- Every table has a primary key: a value that's different in every row and never `NULL`.
- A foreign key holds another table's primary key, such as `Orders.CustomerId`, so tables link by keys instead of copying data.

Until now you've queried databases someone else designed. Soon you'll design your own, and the way the tables are split decides how easy every query, report and screen will be.

## Start with what the data is for

Before you create a single table, answer these:

- **What data do you need?** Not everything you could collect: what the app actually uses.
- **What reports and screens will show it?**
- **What are the most common questions?** "Which pets has this owner brought in?" "What did each vet earn this month?"
- **How will you filter it?** By city, by date, by species?
- **How finely do you need it?** A visit's date, or its date and time?

The answers point to the tables. Design without them, and you'll be reshaping tables after the app is built, which is far harder.

## Learn from Northwind

Northwind is a well-designed database. Look at how it's organized.

### One subject per table

`Customers` holds only facts about customers: name, contact, address. `Orders` holds only facts about orders: dates, freight, which customer, which employee. An order doesn't repeat the customer's name or address; it refers to the customer instead.

Each table is about **one kind of thing**, and each row is **one of those things**.

### Granularity: the smallest useful pieces

A customer's address isn't one long column. It's `Address`, `City`, `Region`, `PostalCode` and `Country`. That's **granularity**: each column holds the smallest piece you'll need on its own.

Imagine finding every customer in London if the whole address were one piece of text. With a `City` column, it's `WHERE City = 'London'`.

The same goes for names: `FirstName` and `LastName` can be sorted, searched and joined into a full name; a single `FullName` column can't easily be taken apart.

### A primary key in every table

Every table has a **primary key** (PK): a column whose value is different in every row, and never `NULL`. Only one product has `ProductId` 1; only one customer has `CustomerId` `'ALFKI'`.

Most tables use a plain number that SQL Server assigns, like `ProductId`. It never needs to change, even if the product's name does.

### Foreign keys link the tables

A **foreign key** (FK) is a column that holds another table's primary key. `Orders.CustomerId` holds the `CustomerId` of the customer who placed the order. That's how an order finds its customer's details without copying them, and it's what you join on.

| Table | Primary key | Foreign keys |
|---|---|---|
| `Customers` | `CustomerId` | |
| `Orders` | `OrderId` | `CustomerId`, `EmployeeId`, `ShipVia` |
| `OrderDetails` | `OrderId` and `ProductId` together | `OrderId`, `ProductId` |
| `Products` | `ProductId` | `SupplierId`, `CategoryId` |

## Names

Pick a naming style and keep it. This course follows Northwind's: tables named in the plural (`Customers`), columns in PascalCase (`PostalCode`), and keys named after their table (`CustomerId` in `Customers`, and the same name wherever it's a foreign key).

## Check yourself

2 questions to try on the lesson page.

## So what

Good design is mostly common sense: decide what the data is for, give each subject its own table, store values in their smallest useful pieces, and link tables by keys instead of copying data between them.

## Key terms

- [Granularity](https://www.ask-codey.com/glossary/granularity)
- [Primary Key](https://www.ask-codey.com/glossary/primary-key)
- [Foreign Key](https://www.ask-codey.com/glossary/foreign-key)

## Cheat sheets

- [Common Sense Design](https://www.ask-codey.com/cheat-sheets/foundations-of-database-design-common-sense-design-in-sql-server-cheat-sheet)

Source: https://www.ask-codey.com/lessons/foundations-of-database-design-common-sense-design-in-sql-server