SQL · Lesson 6 of 7

Creating Tables and Design

Data types, constraints, foreign keys and normalization.

• Intermediate
• 15 min read
• 3 objectives

Before this lessonLesson 5: INSERT, UPDATE, DELETE

Before you can query data you have to shape it. Schema design decides which tables exist, what columns they have and how they connect. A good schema prevents whole categories of bugs, because the database itself refuses bad data.

Design before you build

The tables you create decide how easy every future query will be. A good schema stops bad data from ever entering the database (an order for a customer who does not exist, two accounts with the same email) and avoids storing the same fact in many places. Spending ten minutes on design saves months of clean-up.

A simple method: list the things your application knows about (customers, orders, products). Each becomes a table. Then ask how they relate: one customer has many orders, so each order stores its customer's id.

CREATE TABLE

CREATE TABLE customers (
  id      INTEGER PRIMARY KEY,
  email   TEXT    NOT NULL UNIQUE,
  name    TEXT    NOT NULL,
  age     INTEGER CHECK (age >= 0),
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total       NUMERIC(10, 2) NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending'
);

Constraints

• PRIMARY KEY: unique, not null identifier.
• NOT NULL: the value is required.
• UNIQUE: no two rows may share the value (emails, usernames).
• CHECK: a rule every row must satisfy.
• DEFAULT: the value used when none is given.
• REFERENCES (foreign key): the value must exist in the other table. Deleting a referenced customer now fails, or cascades if you declare ON DELETE CASCADE.

Choosing types

Use INTEGER for counts and ids, NUMERIC(p, s) for money (never floating point, which rounds), TEXT or VARCHAR for strings, BOOLEAN, DATE and TIMESTAMP for time. Store times in UTC.

Relationships

• One-to-many: a customer has many orders. Put the foreign key on the "many" side.
• Many-to-many: students and courses. Add a join table holding both foreign keys.
• One-to-one: rare; a foreign key that is also unique.

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_id  INTEGER REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

Normalization

Normalization means storing each fact once. If an orders table repeated the customer's name and address on every row, changing an address would mean editing many rows and risking inconsistency. Split it: customer details in customers, and orders point to them by id. Aim for about third normal form: every column depends on the key, the whole key, and nothing but the key. Denormalize deliberately later only if measurements show you need speed.

Changing a schema

ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers DROP COLUMN phone;
DROP TABLE IF EXISTS old_orders;

A schema that protects itself

Constraints are rules the database enforces for you, so bugs in your application cannot corrupt the data. Here the database itself rejects an order that points at a customer who does not exist.

CREATE TABLE customers (
  id      INTEGER PRIMARY KEY,
  email   TEXT NOT NULL UNIQUE,
  name    TEXT NOT NULL
);

CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(id),
  total        REAL NOT NULL CHECK (total >= 0),
  created_at   TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

• PRIMARY KEY: unique, never null; identifies the row.
• NOT NULL: the value is required.
• UNIQUE: no two rows may share this value (two users cannot register the same email).
• REFERENCES: a foreign key; the value must exist in the other table.
• CHECK: a custom rule, such as a total that cannot be negative.
• DEFAULT: what to store when no value is given.

Seeing constraints in action

Trying to break a rule produces an error instead of bad data. This is exactly what you want.

INSERT INTO orders (customer_id, total) VALUES (999, 10);

Error: FOREIGN KEY constraint failed

INSERT INTO customers (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO customers (email, name) VALUES ('ada@example.com', 'Ada Two');

Error: UNIQUE constraint failed: customers.email

Many-to-many needs a bridge table

A student can join many courses and a course has many students. You cannot store that in either table alone, so you add a third table holding one row per pairing.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses  (id INTEGER PRIMARY KEY, title TEXT NOT NULL);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id  INTEGER NOT NULL REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)   -- a student can enrol only once per course
);

Key takeaways

• Each kind of thing gets its own table; relationships use foreign keys.
• Constraints (NOT NULL, UNIQUE, REFERENCES, CHECK) reject bad data automatically.
• Many-to-many relationships need a bridge table.
• Store each fact once; that is the heart of normalization.

-- Write your solution here