Core Relational Database Concepts

Before writing a single line of SQL, you must understand the core vocabulary of relational databases. This chapter covers the foundational building blocks — Tables, Rows, Columns, Keys, and Relationships — the same concepts that underpin every MySQL database you’ll ever build.

Tables: The Basic Unit of Storage

A table is the fundamental storage structure in a relational database. Think of it like a spreadsheet with a strictly defined format: every row holds one record, and every column holds one specific type of data. Each table represents a single real-world entity — a customer, a product, an order.

📋 customers
customer_id PK first_name NN last_name NN email UK city
1PriyaSharmapriya@example.comMumbai
2ArjunPatelarjun@example.comDelhi
3SnehaReddysneha@example.comHyderabad
4RahulGuptarahul@example.comBengaluru

PK = Primary Key  ·  UK = Unique Key  ·  NN = NOT NULL

Anatomy of a Table
T
Table
A named, two-dimensional grid of data representing one entity type. Example: customers, orders, products
R
Row (Record / Tuple)
A single horizontal entry representing one instance of the entity. Each row in customers = one customer’s complete data.
C
Column (Field / Attribute)
A vertical set of values, all of the same data type. Each column has a name and a type: email VARCHAR(255)

Good design rule: Each table should represent one and only one entity type. Mixing customers and orders in a single table leads to data redundancy and update anomalies — this is why we normalise databases.

Keys: The Backbone of Data Integrity

Keys are special column(s) that serve specific roles in a table — they identify records uniquely, enforce constraints, and link tables together. Understanding keys is essential before working with any real-world database.

🔑 Primary Key (PK)
Uniquely identifies every row in a table. Cannot be NULL. Every table should have exactly one Primary Key. MySQL creates a clustered index on the PK automatically.
customer_id INT PRIMARY KEY AUTO_INCREMENT
🔗 Foreign Key (FK)
A column that references the Primary Key of another table. Enforces referential integrity — you can’t reference a customer that doesn’t exist.
customer_id INT REFERENCES customers(customer_id)
🛡️ Unique Key (UK)
Ensures all values in the column are distinct — no duplicates. Unlike PK, a Unique Key can allow one NULL value. Used for email, username, national ID, etc.
email VARCHAR(255) UNIQUE
🔲 Composite Key
A Primary Key made from two or more columns together. Used in junction tables (many-to-many relationships). The combination must be unique, not the individual columns.
PRIMARY KEY (student_id, course_id)

Natural Key vs Surrogate Key: A Natural Key uses real-world data as the PK (e.g., email or passport number). A Surrogate Key is an artificial auto-incremented integer (e.g., id INT AUTO_INCREMENT). In practice, surrogate keys are almost always preferred because natural keys can change, be NULL, or be too long to index efficiently.

Relationships: Linking Tables Together

The true power of relational databases comes from relationships — the ability to connect data across multiple tables. This avoids storing the same data twice (redundancy) and keeps data consistent. There are three types of relationships:

1. One-to-Many (1:N) — The Most Common

One record in Table A can relate to many records in Table B, but each record in Table B relates to only one record in Table A. This is by far the most common relationship type. The Foreign Key is placed on the “many” side.

Example: One customer can place many orders. Each order belongs to exactly one customer.

One-to-Many: customers → orders
customers (1)
🔑 customer_id (PK)
first_name
email
──────
1 : N
──────
orders (Many)
🔑 order_id (PK)
🔗 customer_id (FK)
order_date
total_amount
The Foreign Key (customer_id) lives in the orders table — on the “many” side.

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

One record in Table A corresponds to exactly one record in Table B, and vice versa. Used to split a table for performance or security reasons (e.g., keep sensitive data like passwords in a separate table).

Example: Each user has exactly one profile. Each profile belongs to exactly one user.

One-to-One: users → user_profiles
users (1)
🔑 user_id (PK)
username
password_hash
──────
1 : 1
──────
user_profiles (1)
🔑 profile_id (PK)
🔗 user_id (FK, UNIQUE)
bio
avatar_url
The FK on the user_profiles side has a UNIQUE constraint — ensuring only one profile per user.

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

Many records in Table A can relate to many records in Table B. This cannot be represented directly with two tables — a junction table (also called a bridge or linking table) is required to connect them. The junction table holds the Foreign Keys from both sides.

Example: A student can enrol in many courses. Each course can have many students enrolled.

Many-to-Many: students ↔ courses (via enrolments)
students (M)
🔑 student_id (PK)
full_name
email
──────
1 : N
──────
enrolments (Junction)
🔗 student_id (FK)
🔗 course_id (FK)
enrol_date
PK = (student_id, course_id)
──────
N : 1
──────
courses (N)
🔑 course_id (PK)
course_name
credits
The junction table uses a Composite Primary Key — (student_id, course_id) together must be unique.

The Golden Rule: You can always identify the correct relationship by asking two questions:
“Can one A have multiple B’s?” and “Can one B have multiple A’s?”
If both answers are YES → M:N (need a junction table). If only one is YES → 1:N. If both are NO → 1:1.

Key Takeaways

  • A table stores data for one entity type in rows (records) and columns (fields).
  • A Primary Key uniquely identifies each row — every table needs one. AUTO_INCREMENT integers are the preferred approach in MySQL.
  • A Foreign Key references a Primary Key in another table, enforcing referential integrity between related data.
  • A Unique Key ensures no duplicate values in a column while allowing NULLs; a Composite Key uses multiple columns together as a unique identifier.
  • The three relationship types are 1:1 (one-to-one), 1:N (one-to-many — most common), and M:N (many-to-many — requires a junction table).
  • In a 1:N relationship, the Foreign Key always lives on the “many” side of the relationship.

What’s Next?

In Chapter 1.4 — Introduction to SQL, we’ll learn what SQL actually is, explore its five sublanguages (DDL, DML, DQL, DCL, TCL), and write our first real SQL statements — the commands that will drive everything we build in this course.