Relational Database Concepts

Relational databases work because they encourage a simple idea: store each kind of fact once, and connect facts through keys.

This section gives you the vocabulary and mental model you'll use throughout the rest of the course.

Core Concepts

The Relational Building Blocks

Concept	What it is	Think of it as
Table	A collection of similar things	A list with a fixed set of fields
Row	One item in a table	One record (one contact, one order)
Column	One field on every row	One attribute (name, email)
Primary key	A unique identifier for a row	A stable ID you can reference
Relationship	A connection between tables	"This order belongs to that customer"

A Relationship Example (Conceptual)

This ERD shows a one-to-many relationship: one contact can have many phone numbers.

erDiagram
  CONTACT ||--o{ PHONE_NUMBER : has

  CONTACT {
    integer contact_id PK
    text full_name
  }

  PHONE_NUMBER {
    integer phone_id PK
    integer contact_id FK
    text phone
    text label
  }

Why Relationships Matter: Less Duplication

If you repeat the same "customer" information on every order row, you create update problems.

-- This result set shows a common duplication problem.
-- The same email appears on multiple order rows.
VALUES
  (1001, 'ada@example.com', 'INV-001', 39.99),
  (1001, 'ada@example.com', 'INV-002', 12.50);

column1	column2	column3	column4
1001	ada@example.com	INV-001	39.99
1001	ada@example.com	INV-002	12.50

In a relational design, you'd typically store the customer once and reference them from orders.

customers.customer_id	customers.email
1001	ada@example.com

orders.invoice_no	orders.customer_id	orders.total
INV-001	1001	39.99
INV-002	1001	12.50

SQLite-Specific Nuances

SQLite Nuance

SQLite can enforce relationships (foreign keys), but enforcement depends on configuration.

You'll learn how to enable and use foreign keys safely in Foreign keys.

Common Pitfalls / Best Practices

Pitfall

Storing multiple values in one column (like "red,blue,green").

It looks convenient at first, but it makes searching, updating, and validating your data much harder.

Best Practice

Keep values atomic (one fact per column) and model repeating data with separate tables.

That is the foundation of clean joins, correct counts, and simpler updates.

Quick Challenge

You have two tables: authors and posts. One author can write many posts.

Which side is "one" and which side is "many"?

View Solution

• authors is the "one" side
• posts is the "many" side

erDiagram
  AUTHOR ||--o{ POST : writes

  AUTHOR {
    integer author_id PK
    text name
  }

  POST {
    integer post_id PK
    integer author_id FK
    text title
  }