CREATE TABLE and schema design

A table is a promise about a kind of fact. Good schema design starts by deciding what one row represents, then choosing a stable identifier, clear columns, and rules that protect the meaning of that row.

Start with the thing the table represents

Do not begin with a list of columns. Begin with a sentence: “One row in courses represents one course we offer.” That sentence is the table's grain—the level of detail of a row. It prevents a common mistake: mixing several kinds of facts in one table because they happen to be needed on the same screen.

For example, a course has a title and level. A lesson belongs to one course and has a position. A learner can enroll in many courses, while each course has many learners. Those are different facts, so they eventually need different tables connected by keys.

Read a CREATE TABLE statement

Inside the parentheses, each line defines one column: a name, a type, and optional rules. The database stores these definitions as the schema. In this browser lesson, PRAGMA table_info shows SQLite's view of the created table. Other databases provide their own catalog views or information-schema queries.

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  published_year INTEGER
);

PRAGMA table_info(books);

Edit the query, predict the rows it will return, then run it.

Choose names that explain the data

Names are part of the interface. Prefer singular table names such as course or plural names such as courses consistently; either convention can work. Use nouns for tables and columns: published_on, not publish. Make identifiers explicit: course_id says more than a generic id once a query joins several tables.

Use a name that remains true as the application grows. A column called status is fine when its allowed values are documented; active_flag is clearer than active when it is stored as a true/false value. Avoid names that encode a temporary UI label or an implementation accident.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  level TEXT NOT NULL,
  published_on TEXT NOT NULL,
  is_published INTEGER NOT NULL DEFAULT 0
);

INSERT INTO courses (course_id, title, level, published_on, is_published)
VALUES
  (1, 'SQL foundations', 'beginner', '2026-09-17', 1),
  (2, 'Schema design', 'intermediate', '2026-10-01', 0);

SELECT course_id, title, level, published_on, is_published
FROM courses
ORDER BY course_id;

Edit the query, predict the rows it will return, then run it.

Choose a stable primary key

A primary key identifies exactly one row. It is the database's durable handle for that record and the value other tables use to refer to it. In this SQLite example, INTEGER PRIMARY KEY gives each learner an integer identifier. In PostgreSQL, you might use an identity column or a UUID; the important design decision is stability, not the spelling of the syntax.

An email address may look unique today, but people can change addresses. A title can change. A stable primary key lets those business values change without changing every relationship that points to the record. You can still add UNIQUE to protect an email as a separate business rule.

CREATE TABLE learners (
  learner_id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

INSERT INTO learners (learner_id, full_name, email)
VALUES
  (101, 'Amina Idrissi', 'amina@example.com'),
  (102, 'Bilal Karim', 'bilal@example.com');

SELECT learner_id, full_name, email
FROM learners
ORDER BY learner_id;

Edit the query, predict the rows it will return, then run it.

Choose types for the values you mean

A type communicates intent and controls what operations make sense. Use integer types for counts and whole-number money in the smallest unit, text for labels and formatted identifiers, date or timestamp types for time, and boolean types for true/false states when your database supports them. The browser runner uses SQLite, where booleans are commonly represented by 0 and 1; PostgreSQL has a native BOOLEAN type.

Do not store a number as text just because it arrived from a form, and do not store a date as a human-facing sentence. A good rule is: store data in a form the database can compare, sort, validate, and calculate with. Format it for people in the application layer.

CREATE TABLE lessons (
  lesson_id INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  position INTEGER NOT NULL,
  is_published INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO lessons (lesson_id, course_id, title, position)
VALUES (1, 1, 'Your first SELECT query', 1);

SELECT lesson_id, title, position, is_published, created_at
FROM lessons;

Edit the query, predict the rows it will return, then run it.

Constraints turn assumptions into rules

Constraints protect a table even when data arrives from a script, an admin panel, an import, or a future service. They are not a replacement for friendly application validation; they are the final shared boundary that makes invalid data impossible to store.

• NOT NULL means a value is required.
• UNIQUE prevents duplicate values or duplicate combinations.
• CHECK accepts only values that satisfy a stated condition.
• DEFAULT supplies a value when an insert intentionally leaves the column out.

Write rules that match real business meaning. A non-negative price should be enforced with CHECK (price_cents >= 0); an optional description should not be marked NOT NULL merely because the first screen happens to collect it.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  sku TEXT NOT NULL UNIQUE,
  is_active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO products (product_id, name, price_cents, sku)
VALUES
  (1, 'SQL notebook', 1499, 'NOTE-SQL-001'),
  (2, 'Database sticker', 299, 'STICKER-DB-001');

SELECT product_id, name, price_cents, sku, is_active
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

Connect tables with foreign keys

A foreign key says that one table's value must refer to a real row in another table. Here, every lessons.course_id points to a course. This makes the relationship explicit: one course can have many lessons, while each lesson belongs to one course.

The combined UNIQUE (course_id, position) rule adds a second business promise: a course cannot have two lessons in the same position. Notice that this is not the primary key. The primary key identifies one lesson globally; the pair expresses a local rule inside one course.

PRAGMA foreign_keys = ON;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE lessons (
  lesson_id INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  position INTEGER NOT NULL,
  FOREIGN KEY (course_id) REFERENCES courses(course_id),
  UNIQUE (course_id, position)
);

INSERT INTO courses VALUES (1, 'SQL foundations');
INSERT INTO lessons VALUES
  (1, 1, 'Your first SELECT query', 1),
  (2, 1, 'Conditions with WHERE', 2);

SELECT course.title AS course_title, lesson.position, lesson.title AS lesson_title
FROM courses AS course
JOIN lessons AS lesson ON lesson.course_id = course.course_id
ORDER BY lesson.position;

Edit the query, predict the rows it will return, then run it.

Keep the grain of each table consistent

Many schema bugs are really grain bugs. An orders table has one row per order. An order_items table has one row per product line within an order. Put a single order-wide fact, such as shipping address, on the order. Put repeated product-line facts, such as quantity and unit price, on order items.

Repeating a group of columns like product_1, product_2, and product_3 is a warning sign. It makes the number of possible items arbitrary and makes queries harder. A child table lets the database represent any number of items with the same clear row meaning.

CREATE TABLE order_items (
  order_item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0)
);

INSERT INTO order_items VALUES
  (1, 5001, 'SQL notebook', 1, 1499),
  (2, 5001, 'Database sticker', 2, 299),
  (3, 5002, 'SQL notebook', 1, 1499);

SELECT order_id, product_name, quantity, unit_price_cents
FROM order_items
ORDER BY order_id, order_item_id;

Edit the query, predict the rows it will return, then run it.

Design for change without guessing the future

Start with the requirements you know, not every field an imagined future might need. A small, clear table is easier to migrate than a vague table full of unused columns. When requirements change, write a migration, test it on representative data, and decide how existing rows receive any new required value.

Use a unique rule when the business needs one, not because a column feels important. In the course lesson example, a lesson title may repeat across courses, but (course_id, position) must be unique because two lessons cannot occupy the same place in one course.

CREATE TABLE course_lessons (
  lesson_id INTEGER PRIMARY KEY,
  course_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  position INTEGER NOT NULL,
  duration_minutes INTEGER NOT NULL CHECK (duration_minutes > 0),
  UNIQUE (course_id, position)
);

INSERT INTO course_lessons VALUES
  (1, 10, 'Your first SELECT query', 1, 25),
  (2, 10, 'Conditions with WHERE', 2, 35),
  (3, 20, 'Primary keys and relationships', 1, 30);

SELECT course_id, position, title, duration_minutes
FROM course_lessons
ORDER BY course_id, position;

Edit the query, predict the rows it will return, then run it.

Review a table before you ship it

1. Write the one-row meaning in a sentence.
2. Choose a stable primary key.
3. Name columns for the data they hold, not the screen that displays them.
4. Choose types that preserve the operations you need.
5. Mark only truly required values NOT NULL.
6. Add unique, check, default, and foreign-key rules that match real business requirements.
7. Insert realistic examples and read them back with a SELECT.

Independent lab: design a small booking schema

Build a schema for bookable meeting rooms. One row in rooms is one room. One row in bookings is one request for one room on one date. The provided solution creates stable keys, a relationship, sensible defaults, and constraints for capacity, attendee count, and booking status.

Read the table definitions before running them. Then read the final join as a sentence: “Show each booking with the name of the room it references.”

PRAGMA foreign_keys = ON;

-- One row in rooms represents one bookable room.
CREATE TABLE rooms (
  room_id INTEGER PRIMARY KEY,
  room_name TEXT NOT NULL UNIQUE,
  capacity INTEGER NOT NULL CHECK (capacity > 0),
  is_active INTEGER NOT NULL DEFAULT 1
);

-- One row in bookings represents one request for one room on one date.
CREATE TABLE bookings (
  booking_id INTEGER PRIMARY KEY,
  room_id INTEGER NOT NULL,
  booked_for TEXT NOT NULL,
  attendee_count INTEGER NOT NULL CHECK (attendee_count > 0),
  status TEXT NOT NULL DEFAULT 'requested' CHECK (status IN ('requested', 'confirmed', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);

INSERT INTO rooms (room_id, room_name, capacity) VALUES
  (1, 'Atlas', 12),
  (2, 'Rif', 6);
INSERT INTO bookings (booking_id, room_id, booked_for, attendee_count, status) VALUES
  (101, 1, '2026-10-08', 8, 'confirmed'),
  (102, 2, '2026-10-08', 4, 'requested');

SELECT booking.booking_id, room.room_name, booking.booked_for,
  booking.attendee_count, booking.status
FROM bookings AS booking
JOIN rooms AS room ON room.room_id = booking.room_id
ORDER BY booking.booked_for, room.room_name;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Creating a table before deciding what one row represents.
• Using a changeable business value, such as a name or email, as the only identifier.
• Storing repeated columns instead of representing repeated facts as rows in a related table.
• Marking every field NOT NULL even when the fact may genuinely be unknown or optional.
• Relying only on application validation for rules that every writer of the database must respect.
• Choosing destructive foreign-key behavior without agreeing on the business consequences.

Lesson review

• I can state what one row in a table represents before selecting columns.
• I can choose a stable primary key separately from a meaningful business value.
• I can use names and types that communicate the data's purpose.
• I can add NOT NULL, UNIQUE, CHECK, and DEFAULT rules deliberately.
• I can model a one-to-many relationship with a foreign key.
• I can spot when repeated facts need their own related table.