Intermediate 15 min

Creating Tables and Design

Data types, constraints, foreign keys and normalization.

What you will learn

Create tables with constraints
Model one-to-many relations
Normalize a design

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.

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.

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

Migrations

In real projects schema changes are written as versioned migration files (Django, Flyway, Alembic) and applied in order, so every environment stays identical.

Try it yourself

Design tables for a blog: authors, posts and tags, where a post has one author and many tags. Write the CREATE TABLE statements.

Show solution

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title TEXT NOT NULL,
  body TEXT
);
CREATE TABLE tags (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id),
  tag_id  INTEGER REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);