SQL Basics: Table Normalisation and Relationships Explained

When I first started learning SQL, I came across terms like normalisation, primary keys, foreign keys, and relationships. At first, they felt a little abstract but these are the foundations of how databases are designed and why SQL works the way it does.

Image by vectorjuice on Freepik

What is Table Normalisation?

Imagine you’re building a database for job applications. You might be tempted to put everything in one giant table: candidate names, job titles, companies, skills, contact info, and so on.

But that creates a problem: repetition. Every time “Microsoft” appears as a company, you store all the details again. Also, if a candidate applies for multiple jobs, their name and contact info repeat, too.

This is messy and inefficient. Normalisation solves this.

Normalisation is the process of breaking one large, redundant table into multiple smaller, related tables.

For example:

• A Candidates table stores each person only once.
• A Companies table stores each company only once.
• A Jobs table stores job postings.

These tables are then linked using unique IDs instead of repeating information.

The result: cleaner, smaller tables that are easier to manage and update.

Keys in Tables

To make these relationships work, we need keys.

• Primary Key (PK): A unique identifier for each row. Example: customer_id = 123 → only one record has this ID. Rule: It cannot repeat and it cannot be empty.
• Foreign Key (FK): A reference to a primary key in another table. Example: In the Orders table, customer_id links to the Customers table. Rule: It can repeat (because many orders can belong to one customer).

Table Relationships & Cardinality

Cardinality describes how tables relate to each other. There are three main types:

1.One-to-One (1:1)

Each row in Table A relates to only one row in Table B. Example: A passport belongs to exactly one person.

2. One-to-Many (1:N)

One row in Table A can link to many rows in Table B. Example: A customer can have many orders. Achieved by connecting a foreign key in one table to a primary key in another.

3.Many-to-Many (M:N)

Many rows in Table A can relate to many rows in Table B. Example: Students and courses (a student can take many courses, a course can have many students). Usually handled with a junction table (e.g. enrolments).

Relationship Diagrams

To see how these tables connect, we use Entity Relationship Diagrams (ERDs).

For example:

• The customer_id is a primary key in the Customers table.
• That same customer_id becomes a foreign key in the Orders table.

Diagramming these relationships (for example, in MySQL Workbench) makes it easier to understand your database structure and ensures your queries will run smoothly.

Why Relationships Matter

Once you’ve normalised tables and set up relationships, you can do multi-table queries with SQL JOINs.

For example:

SELECT 
  customers.customer_id
FROM customers
  JOIN orders
    ON customers.customer_id = orders.customer_id;

This pulls together customer info and their orders, even though the data lives in two different tables.

That’s the purpose of relational databases: you don’t need to cram everything into one table. Instead, you store data once, link it properly, and let SQL stitch it together whenever you need it.

Key Takeaways

• Normalisation: Break one big table into smaller, related tables to avoid repetition.
• Primary keys: Unique identifiers in each table.
• Foreign keys: References to primary keys in other tables.
• Relationships: Define how tables connect (one-to-one, one-to-many, many-to-many).
• ERDs: Visual maps of your table structure.
• JOINs: Allow you to query across multiple related tables.