# Fundamentals of Database Schema Design: Normalization, Keys, and Indexing

> Chapter 12 of the Courseiva GOOGLE-PCDE curriculum — https://courseiva.com/learn/google-pcde/database-schema-design-fundamentals

**Official objective:** 4.1 — Design relational database schemas with normalization, primary/foreign keys, and indexing strategies.

## Introduction

How do you store customer orders so that a single change doesn't force you to rewrite every record in the entire company? This problem sits at the heart of database schema design, and solving it is the difference between a database that works like a well-organised filing cabinet and one that behaves like a messy junk drawer. For anyone studying for the Google Professional Cloud Database Engineer exam, understanding normalisation, keys, and indexing is not just academic theory – these are the bedrock skills you will use to design the tables that power real-world cloud applications.

## The Personal Library Analogy

When you first start collecting books, you can just toss them onto a single shelf as they arrive. But when your collection grows to hundreds of books, a single pile creates chaos. That first mess leads to a breakdown: you cannot find a specific book when you need it, you end up buying duplicates because you forgot you already owned a copy, and even removing a worn-out book risks causing the whole pile to collapse.

To fix this, you organise your library. First, you sort books into distinct sections by genre – fiction, history, science, biography. This is like database normalisation: grouping related data together into separate tables to reduce repetition and keep things tidy. Next, you give each book a unique catalogue number printed on a sticker on its spine. This number is your primary key – a unique identifier that lets you instantly locate any single book. Finally, you create a card index organised by author name and another by title, so you can find a book even if you forgot the catalogue number. Those card indexes are your database indexes: they speed up searches by giving you a direct path to the shelf location.

Without this system, a library of just 50 books is fine, but a library of 5,000 becomes unusable. Normalisation ensures you store information only once, keys give every row a reliable identity, and indexes make searching fast. Your personal library stays organised, unharmed by chaos, and ready for growth.

## Core explanation

A database schema is the blueprint of your database. It defines how data is organised into tables, which columns each table contains, and how those tables relate to one another. Think of a table as a grid made of rows and columns, similar to a spreadsheet. Each row represents one record – say, one customer or one order. Each column represents a single piece of information about that record, such as the customer's name, email address, or phone number.

Designing a good schema is critical because a poor design leads to data that is duplicated, inconsistent, or slow to retrieve. Two core principles – Normalisation and Keys – ensure your data stays clean. A third concept, Indexing, makes sure you can find that clean data quickly.

Normalisation is a systematic process for organising data to reduce redundancy (unnecessary repetition) and improve data integrity (accuracy and consistency). It involves dividing large tables into smaller, related tables and defining relationships between them. The process is typically broken down into 'normal forms', which are rules a database should follow. The most commonly used are the First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).

First Normal Form (1NF) requires that each column holds a single, indivisible value, and that every row is unique. For example, if you had a 'Customer' column that stored 'John, Jane' for two people, you would violate 1NF because that cell contains multiple values. You would split it into two rows or two separate tables.

Second Normal Form (2NF) builds on 1NF by requiring that every non-key column (a column that is not part of the primary key) be fully dependent on the whole primary key, not just part of it. This only applies when your primary key is made of two or more columns (a composite key). For example, if you had a table with columns OrderID, ProductID, and ProductName, the ProductName depends only on ProductID, not on the whole pair of OrderID and ProductID. That partial dependency violates 2NF. The fix is to split ProductName into a separate Product table.

Third Normal Form (3NF) goes further and requires that every non-key column be directly dependent on the primary key, and not on another non-key column. This is called eliminating transitive dependency. For instance, if a table has columns EmployeeID, DepartmentID, and DepartmentName, the DepartmentName depends on DepartmentID, not directly on EmployeeID. The fix is to keep DepartmentName only in a Department table.

Keys are the special columns that establish identity and relationships. A primary key uniquely identifies each row in a table. It must contain a unique value for every row – no duplicates allowed – and cannot be empty (null). For example, a CustomerID column set to auto-incrementing numbers works perfectly. A foreign key is a column in one table that links to the primary key of another table, creating a relationship between the two. For example, an Orders table might have a CustomerID column that is a foreign key referencing the CustomerID in the Customers table. This ensures every order is linked to a real customer and prevents orphaned orders.

Indexing is the final piece. An index is a special data structure that sits alongside your table, like the index at the back of a textbook. Without an index, to find a specific row the database must scan every single row in the table, one after the other – a full table scan. That works for tiny tables but becomes painfully slow for tables with millions of rows. An index stores a sorted copy of selected columns and a pointer back to the original row. When you search by that column, the database uses the index to jump directly to the right rows, dramatically speeding up read queries.

However, indexes come with trade-offs. They take up extra disk space, and they slow down write operations (INSERT, UPDATE, DELETE) because the index itself must be updated each time the table changes. Therefore, database engineers do not index every column – they index only the columns used in WHERE clauses, JOIN conditions, and ORDER BY statements.

Together, these three concepts form the foundation of relational database design. Normalisation shapes the overall structure, keys define relationships and enforce integrity, and indexing optimises performance. A well-normalised schema with appropriate keys and carefully chosen indexes will scale gracefully, support fast queries, and maintain data accuracy even as your application grows.

## Real-world context

Imagine you are a database engineer at a growing e-commerce company called ShopEase. The company started small, storing all customer and order information in one giant spreadsheet. As ShopEase expands to thousands of daily orders, that spreadsheet becomes a nightmare. Customer names are misspelled differently in different rows, the same customer appears with three variations of their address, and searching for a specific order takes minutes.

You are brought in to redesign the database. Your first step is to sit with the business team and understand all the data they need to store. You learn that the core entities are: Customers, Products, Orders, and Order Items. You begin normalising.

First, you apply 1NF. You split the messy spreadsheet into separate tables. Each column now holds a single value. You create a Customers table with columns CustomerID, FirstName, LastName, Email, and Phone. No more storing multiple phone numbers in a single cell.

Next, you apply 2NF. The Orders table has a composite primary key of OrderID and ProductID. You spot that ProductName and ProductPrice depend only on ProductID, not on the full composite key. You create a separate Products table with ProductID as its primary key, and move ProductName and ProductPrice there. Your Orders table now only stores OrderID, CustomerID, and OrderDate. A separate OrderItems table stores OrderID, ProductID, and Quantity.

You apply 3NF next. In the Customers table, you notice a column called 'CityPopulation'. That value depends on the City column, not directly on CustomerID. You create a Cities table with City and CityPopulation, and store just City in the Customers table. This prevents you from updating hundreds of customer rows whenever a city's population changes.

With your normalised schema in place, you define keys. You set CustomerID as the primary key for Customers, ProductID for Products, and a combination of OrderID and ProductID as the composite primary key for OrderItems. You add foreign key constraints to enforce relationships: the CustomerID in Orders must reference a valid CustomerID in Customers, and the ProductID in OrderItems must reference a valid ProductID in Products. These constraints prevent data entry errors and maintain referential integrity.

Finally, you index strategically. You know the application runs reports that filter orders by OrderDate, so you create an index on that column. Customers are often searched by Email, so you index that too. You avoid indexing every column because you know heavy writes on the OrderItems table would slow down with too many indexes. You use the database's EXPLAIN command to test your queries and confirm they are using your indexes.

The result? A database that loads customer details instantly, finds orders in milliseconds, and ensures that when a customer updates their address, it changes in exactly one place. The schema is clean, fast, and ready to scale to a million orders without crumbling.

## Exam focus

The Google Professional Cloud Database Engineer (PCDE) exam tests your understanding of schema design principles in the context of Cloud SQL, Cloud Spanner, and BigQuery. Expect multiple-choice and scenario-based questions that require you to apply normalisation rules, identify correct key usage, and choose appropriate indexing strategies.

The exam loves to test normalisation forms, especially 1NF, 2NF, and 3NF. You will see a table description and be asked: 'Which normal form is violated?' or 'Which step should be taken to achieve 3NF?' The traps are subtle. They might show you a table that satisfies 1NF and 2NF but violates 3NF due to a transitive dependency. Another common trap is a table that has no duplicate rows but still violates 1NF because a column contains multiple values in a single cell.

Key concepts appear heavily. You must know the difference between a primary key, a foreign key, and a unique key constraint. A frequent trap: they give you a proposed primary key that contains null values or allows duplicates, which is incorrect. They also test that a foreign key can reference only a primary key or a unique key column, and that it can be optional (allowing nulls) or mandatory. Do not confuse a foreign key constraint with an index; they serve different purposes.

Indexing questions focus on when to use an index and when not to. The exam expects you to know that indexes speed up SELECT queries but slow down INSERT, UPDATE, and DELETE operations. A common pitfall is indexing every column 'just in case', which wastes storage and hurts write performance. They will give you a query pattern and ask which column to index. The correct answer is always the column used in the WHERE clause or JOIN condition, not the one in the SELECT list.

Specific exam-relevant concepts you must memorise:

- Denormalisation: the intentional opposite of normalisation, used in data warehouses (like BigQuery) to reduce JOIN operations at the cost of redundancy. The exam expects you to recognise when denormalisation is appropriate (analytics, reporting) versus when it is harmful (OLTP transactions).
- Composite indexes: indexes that include multiple columns. The order of columns matters – the leftmost column must be used in the query for the index to be effective.
- Clustered indexes: a type of index that physically reorders the table data to match the index order. Cloud Spanner and some MySQL engines support this.
- Covering indexes: an index that includes all columns needed for a query, so the database never has to touch the original table. Expect one question about this.

Finally, be ready for questions that combine schema design with Google Cloud specifics. For example, they might ask: 'You are migrating a normalised schema to Cloud Spanner. Spanner recommends denormalisation for high read performance. Which design change should you make?' The answer will involve adding redundant columns to reduce interleaved table joins. Practise these hybrid questions by studying the documented best practices for Cloud SQL, Cloud Spanner, and BigQuery.

## Step by step

1. **Identify all entities and attributes** — List every distinct 'thing' your application manages (e.g., Customers, Products, Orders, Payments) and every piece of information about each thing (e.g., CustomerName, Email, Phone). This step forms the raw material for your tables and columns.
2. **Apply 1NF – ensure atomic values and a unique identifier** — Examine each column: does any column contain multiple values in a single row (e.g., a 'Phones' column holding two phone numbers)? If yes, split those into separate rows or a related table. Then assign a single primary key column (like CustomerID) that uniquely identifies every row.
3. **Apply 2NF – remove partial dependencies on composite keys** — If your table has a composite primary key (e.g., OrderID and ProductID), check every non-key column. Does it depend on part of the key rather than the whole key? For example, ProductName depends only on ProductID. If so, split that column into a new table where the whole key is just ProductID.
4. **Apply 3NF – remove transitive dependencies** — Look for columns that depend on another non-key column rather than directly on the primary key. For instance, if CityPopulation depends on City, create a separate Cities table with City as the primary key and CityPopulation as a column. Keep only City (as a foreign key) in the original table.
5. **Define primary and foreign key constraints** — For each table, set a primary key column that is unique and not null. Then add foreign key columns in child tables (e.g., CustomerID in Orders) that reference the primary key of the parent table (e.g., CustomerID in Customers). Enforce these constraints to prevent orphan records.
6. **Design and implement indexing strategy** — Analyse the most common queries your application runs. Identify the columns used in WHERE filters, JOIN conditions, and ORDER BY clauses. Create single-column or composite indexes on those columns. Avoid indexing columns rarely used in queries. Test query performance using the EXPLAIN command to confirm indexes are being used.

## Comparisons

### Primary Key vs Foreign Key

**Primary Key:**
- Uniquely identifies each row in its own table
- Cannot contain null values
- Only one primary key per table

**Foreign Key:**
- Links a row to a primary key in another table
- Can contain null values (optional relationship)
- Multiple foreign keys per table allowed

### Clustered Index vs Non-Clustered Index

**Clustered Index:**
- Physically reorders the table data to match index order
- Only one clustered index per table
- Faster for range queries on the indexed column

**Non-Clustered Index:**
- Stores a separate structure with pointers to table rows
- Multiple non-clustered indexes per table allowed
- Slower for range queries but easier to maintain

### Normalisation (3NF) vs Denormalisation

**Normalisation (3NF):**
- Reduces data redundancy to near zero
- Increases number of tables and JOIN operations
- Best for transactional (OLTP) systems with frequent writes

**Denormalisation:**
- Introduces controlled redundancy for read speed
- Reduces number of tables and JOINs
- Best for analytical (OLAP) systems with frequent reads

### Single-column Index vs Composite Index

**Single-column Index:**
- Indexes exactly one column
- Works for queries filtering by that one column
- Simpler to create and maintain

**Composite Index:**
- Indexes two or more columns in order
- Works only if the leftmost column is in the query
- More efficient for queries filtering on multiple columns

## Diagram

_Flowchart showing the steps from an unnormalised spreadsheet to an optimised relational database schema through normalisation, key definition, and indexing._

```mermaid
flowchart TD
    A[Unnormalised data: single spreadsheet with repeats] --> B[Apply 1NF: atomic columns + primary key]
    B --> C[Apply 2NF: remove partial dependencies]
    C --> D[Apply 3NF: remove transitive dependencies]
    D --> E[Normalised schema: multiple related tables]
    E --> F[Define primary and foreign keys]
    F --> G[Create indexes on query columns]
    G --> H[Optimised database: clean, fast, scalable]
```

## Common misconceptions

- **Misconception:** Normalisation always makes databases faster, so you should always aim for the highest normal form, like 5NF. **Reality:** Normalisation reduces redundancy but often increases the number of tables and JOINs, which can slow down read queries. The goal is typically 3NF for transactional systems; higher normal forms are rare and only used in specific academic or data-modelling contexts. (Beginners equate 'clean' with 'fast' and do not understand the performance trade-off between fewer tables (denormalised) and more JOINs (normalised). Real-world engineers strike a balance.)
- **Misconception:** A primary key and a unique key are the same thing because both enforce uniqueness. **Reality:** A primary key uniquely identifies a row and cannot contain null values. A unique key also enforces uniqueness but can have a single null value (in most databases). A table can have only one primary key but multiple unique keys. (The names sound interchangeable in casual conversation, and instructional materials sometimes blur the line. The exam explicitly tests this subtle difference.)
- **Misconception:** Creating an index on every column is a good way to make all queries fast. **Reality:** Each index consumes storage and slows down write operations (INSERT, UPDATE, DELETE) because the index must be updated every time the table changes. Indexing every column would cripple write performance and waste resources. Index only columns used in WHERE, JOIN, and ORDER BY clauses. (Beginners assume more indexes = more speed, not realising that indexes are not free. The trade-off is not intuitive to someone new to databases.)
- **Misconception:** A foreign key constraint automatically creates an index on the foreign key column. **Reality:** Foreign key constraints enforce referential integrity but do not automatically create an index. You must create the index separately if you want to speed up queries that join on that foreign key. Some databases (like MySQL) do create an index implicitly, but many (like PostgreSQL) do not. (People see the word 'key' in foreign key and assume it comes with an index. The exam tests this misunderstanding frequently.)
- **Misconception:** 1NF means the table has no duplicate rows. **Reality:** 1NF specifically requires atomic columns (each cell holds a single value) and a unique identifier for each row (a primary key). Having no duplicate rows is a property of all normalised tables, but the defining feature of 1NF is avoiding multi-valued cells. (Beginners memorise 'no duplicates' as the rule for all normal forms without understanding the specific atomic value rule of 1NF.)

## Key takeaways

- Normalisation to at least 3NF is the standard practice for transactional (OLTP) databases to eliminate data redundancy and prevent update anomalies.
- A primary key must be unique, non-null, and unchanging for the lifetime of the row; a foreign key links two tables and must reference a primary or unique key in the parent table.
- Indexes drastically speed up SELECT queries but impose a write-performance penalty, so only index columns used in WHERE, JOIN, and ORDER BY clauses.
- Denormalisation (intentionally adding redundancy) is acceptable and expected in analytical databases like BigQuery to reduce JOIN overhead, but it is harmful in high-volume transactional systems.
- A composite index indexes multiple columns; the order of columns in the index definition determines which queries it can accelerate – the leftmost column must appear in the query.
- Foreign key constraints enforce data integrity but do not automatically create indexes; you must create indexes separately on foreign key columns to speed up JOIN queries.
- 1NF requires atomic values in each column; 2NF removes partial dependencies on composite keys; 3NF removes transitive dependencies on non-key columns.
- The EXPLAIN plan feature in Cloud SQL and other databases reveals whether a query is using an index or performing a full table scan – essential for performance tuning.

## FAQ

**Does normalising a database always make it faster?**

No, normalisation reduces data duplication and improves consistency, but it often increases the number of JOIN queries needed to retrieve related data, which can slow down read operations. It is a trade-off: normalise for data integrity, denormalise for read performance when needed.

**What is the difference between a primary key and a unique key?**

A primary key uniquely identifies each row and cannot contain a null value. A unique key also enforces uniqueness but allows one null value (in most databases). A table can have only one primary key but multiple unique keys.

**Do I need to create an index for every foreign key?**

You should create an index on every foreign key column if you frequently JOIN on that column or filter by it. The foreign key constraint itself does not create an index, so queries joining the two tables would be slow without it.

**What is the third normal form (3NF) in simple terms?**

3NF means that every non-key column in a table depends solely on the primary key, and not on any other non-key column. In other words, you should have no 'chain' of dependencies – for example, storing a department manager's name in an employee table because it depends on the department, not on the employee directly.

**Can I have too many indexes on a table?**

Yes. Each index takes up disk space and slows down INSERT, UPDATE, and DELETE operations because the index must be updated every time the table changes. Over-indexing can cripple write-heavy applications. Index only columns that appear in WHERE, JOIN, and ORDER BY clauses.

**What is a composite index and how do I use it correctly?**

A composite index is an index on two or more columns. For it to be effective, your query must use the leftmost column in the index definition. For example, an index on (City, LastName) will accelerate a query filtering by City, but not one filtering only by LastName.

**When should I denormalise in a Google Cloud database?**

You should denormalise when using analytical databases like BigQuery or high-read-throughput databases like Cloud Spanner. In these systems, reducing JOINs by adding redundant columns often yields faster query performance, even at the cost of storage and update complexity.

## Check your understanding

1. **A table has columns OrderID (primary key), ProductID, ProductName, Quantity. ProductName depends only on ProductID, not on OrderID. Which normal form violation does this represent?**

   Answer: This is a 2NF violation (partial dependency) because ProductName depends on part of the composite key (ProductID) rather than the whole key (OrderID + ProductID).

2. **What is the difference between a clustered index and a non-clustered index?**

   Answer: A clustered index physically reorders the table data to match the index order, and a table can have only one. A non-clustered index stores a separate data structure with pointers to rows, and a table can have many.

3. **When should you denormalise a database schema in a Google Cloud context?**

   Answer: Denormalise when using analytical databases like BigQuery or high-read-throughput systems like Cloud Spanner to reduce JOIN overhead and improve read performance, at the cost of storage and update complexity.

4. **Can a foreign key column contain duplicate values?**

   Answer: Yes, a foreign key column can contain duplicate values because it simply references a primary key in another table; many rows can reference the same parent row.

5. **Why should you not index every column in a database table?**

   Answer: Each index consumes disk space and slows down write operations (INSERT, UPDATE, DELETE) because the index must be updated on every change. Over-indexing harms write-heavy applications.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/google-pcde/database-schema-design-fundamentals