Contents7 sections
In part 1, you learned the parts of a database: tables, rows, columns, and keys. Now you will plan your own first database. On paper. Before you touch any software.
Paper first is not old-fashioned. It is faster. Mistakes on paper cost a rubber stamp. Mistakes in software cost rebuilds. The method below follows the design process in Microsoft’s database design guide, written for first-time database builders.
You need one sheet of paper, a pencil, and an idea. The film collection from part 1 is used as the running example.
Step 1: Write down the purpose
Write one or two sentences that say what the database is for, how you expect to use it, and who will use it.
Example: “This database keeps track of my film collection and my viewings, so I can see what I own, who directed it, and what I watched and thought of it.”
That sentence focuses every later decision. When you are unsure whether something belongs in the database, check it against the purpose. If it does not serve the purpose, leave it out.
Step 2: List the things to track
Start from paper you already have. A form, a list, a notebook page. List every box on it. Each box is a potential column.
No form? Imagine the one you would design. Then list what each report or answer would need to show, and work backwards to the items required.
For the film collection, the list might read: film title, year released, director first name, director last name, director birth year, runtime, date watched, rating, who I watched with.
Do not aim for perfect at this stage. You will fine-tune the list as you go.
Step 3: One subject per table, smallest useful parts
Group the list into major subjects. Each subject becomes a table. Columns in a table must store facts only about that table’s subject.
The film list splits into three subjects: films, directors, and viewings. Three tables. A film’s runtime belongs in the Films table. A director’s birth year belongs in the Directors table. A viewing’s date belongs in the Viewings table.
Now break each piece of information into its smallest useful parts. One column per type of information. The test is simple: if you want to sort, search, calculate, or report on an item, it needs its own column.
That is why first name and last name are separate columns. Stored as one “name” column, you could not sort by last name. An address splits the same way: street, town, postcode, and country each get their own column.
Two more rules belong here:
- Record each fact just once. If the same fact appears in more than one place, put it in its own table and link to it. Repeating a fact wastes space, and sooner or later one copy gets updated and the other does not.
- One value per cell. No lists in cells. No numbered columns like Phone 1, Phone 2, Phone 3. When one thing has several values of the same kind, they become separate rows, in a separate table if needed. The formal name for this rule is First Normal Form, often written 1NF. You do not need the name to use the rule. You only need: one value per cell, no lists, no repeating groups.
Step 4: Give every row a unique identifier
Choose a primary key for each table. A primary key is the column, or columns, that uniquely identifies each row. The beginner case is one column: a product ID, an order ID, a film ID.
If nothing natural is unique, invent one. A running number is the standard answer: 1, 2, 3.
Names alone are bad identifiers. Two people can share a name. A dedicated ID column avoids the collision entirely. Think of it as a label that belongs to one row only, like a serial number.
In the film collection: film_id identifies each film. director_id identifies each director. viewing_id identifies each viewing. The director_id in the Films table and the film_id in the Viewings table are foreign keys. They are how the tables connect.
Step 5: Test the design with sample rows
Before you build anything, write out a few rows of sample data by hand. Then check whether the answers you want come out of the tables as expected.
Try these questions against the film collection tables from part 1:
- Which films do I own, and who directed each one? Combine Films and Directors on
director_id. - What did I watch in March 2026, and how did I rate it? Read the Viewings table.
- Which film have I watched twice? Jaws appears in two viewing rows.
If a question has no clean answer, adjust the design on paper. Add a column. Split a table. Move a fact. This is the cheapest moment to find problems.
One more tip, from Microsoft’s design guide: in most cases, do not store the result of a calculation in a table. Let the database work the answer out when you ask for it. A stored total goes stale the moment its inputs change.
What comes next
The full design process has three more steps after this point: setting up the table relationships properly, refining the design, and applying the deeper normalization rules. Those are later articles in this series. You do not need them to build a sound first database. Steps 1 to 5, done on paper, will carry you further than most first attempts get.
Your paper plan is now a blueprint. When you open your first database program, you will already know your tables, your columns, and your keys. The software becomes typing, not deciding.
Keep going
You planned on paper. The next step is building. When your first site is ready to meet the world, run through the before-you-launch checklist before it goes live. And if any term in this series was new, part 1 is the place to look it up.