Tables, Rows, Columns, and Schemas

A table is not simply a spreadsheet stored on a server. It is a named contract for one kind of fact. This lesson teaches you to state what one row means, give every column one clear job, inspect the schema the database actually accepted, write explicit records, and plan changes without treating production data as disposable.

A table is a contract at four levels

Read a table as a blueprint, not a rectangle

A table should model one coherent subject. If its name is products, one row should represent one product—not a product in some rows, a supplier in others, and a subtotal in a final row. This exact meaning is the table’s grain. When grain is unclear, duplicates, partial updates, and confusing queries follow.

The visual below separates the contracts hidden inside a familiar table. The table name identifies the subject. Each column names one attribute of that subject. Constraints narrow the values the database will accept. Rows are current observations that must obey the blueprint.

Turn the blueprint into a real table

This runnable example expresses the visual as SQLite SQL. Read each line before running it. The first column provides stable row identity. The SKU is a required unique business value. Money is stored as integer cents so values such as 79.99 do not depend on binary floating-point rounding. The state flag has an explicit default and a bounded set of accepted values.

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

INSERT INTO products
  (product_id, sku, name, price_cents)
VALUES
  (101, 'KB-001', 'Mechanical Keyboard', 7999),
  (102, 'MS-001', 'Wireless Mouse', 3499);

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

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

Rows are records, not positions

A row groups attributes that describe one occurrence at the chosen grain. Its identity comes from data—normally a primary key—not from being “row 7.” Deleting a row or changing an execution plan can change the order in which records happen to appear. If a consumer needs a stable sequence, the query must request it with ORDER BY.

Rows should not contain presentation-only separators, totals mixed into detail data, or several logical records packed into one text value. A spreadsheet might include a blank line or “TOTAL” row for reading; a relational table keeps records consistent and asks a query to calculate or format a report.

A column needs one meaning and one contract

A useful column name tells readers what a value means without opening application code. Prefer price_cents to value1, published_at to date, and billing_email to a generic text. Include the unit when ambiguity could cause a real defect. Names become a long-lived API used by queries, migrations, reports, and integrations.

The declared type describes a broad family of values. NOT NULL says absence is invalid. DEFAULT supplies a value when the writer omits that column; it does not repair an explicitly invalid value. UNIQUE protects candidate identifiers, while CHECK expresses row-level rules. The next lesson explores types and NULL in depth.

Write rows with explicit column lists

Name the target columns in every ordinary INSERT. The second insert below deliberately lists columns in a different order to prove that the names—not table-definition position—control the mapping. The first insert omits room and published, so their declared defaults apply.

CREATE TABLE course_sessions (
  session_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  room TEXT NOT NULL DEFAULT 'Online',
  seats INTEGER NOT NULL CHECK (seats BETWEEN 1 AND 100),
  published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1))
);

INSERT INTO course_sessions
  (session_id, title, seats)
VALUES
  (1, 'SQL Foundations', 30);

INSERT INTO course_sessions
  (title, session_id, room, seats, published)
VALUES
  ('Schema Workshop', 2, 'Lab A', 18, 1);

SELECT session_id, title, room, seats, published
FROM course_sessions
ORDER BY session_id;

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

Schema means blueprint—and sometimes namespace

Developers use schema in two related ways. First, it means the database blueprint: tables, columns, types, constraints, indexes, and relationships. In PostgreSQL and several other systems, a schema is also a named namespace inside a database, so sales.orders and support.orders can be distinct tables. SQLite has a simpler model and commonly uses main and temp database namespaces.

Inspect what the database accepted

Do not rely only on the migration file you intended to run. The live catalog is the database’s account of its current structure. SQLite exposes a schema table and table-valued pragma functions. PostgreSQL exposes standard information_schema views plus richer pg_catalog metadata. MySQL also provides information_schema. Learn the catalog for the engine you operate.

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

SELECT name, type, "notnull", dflt_value, pk
FROM pragma_table_info('products')
ORDER BY cid;

SELECT name, sql
FROM sqlite_schema
WHERE type = 'table' AND name = 'products';

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

Schema changes are data changes with a longer memory

An ALTER TABLE changes the contract for existing and future rows. Adding a required column raises an immediate question: what value should old rows receive? In the example, status has an honest default, so existing members can satisfy the new rule. When no honest default exists, a safer migration may add a nullable column, backfill valid values in batches, verify the data, and only then make the column required.

CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

INSERT INTO members (member_id, email) VALUES
  (1, 'ada@example.test'),
  (2, 'grace@example.test');

ALTER TABLE members
ADD COLUMN status TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'paused'));

UPDATE members
SET status = 'paused'
WHERE member_id = 2;

SELECT member_id, email, status
FROM members
ORDER BY member_id;

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

1. 01
   Describe the invariant

   State why the new structure is needed and which old and new records must remain valid.

2. 02
   Inspect real data

   Measure nulls, duplicates, ranges, volume, and application versions before assuming the migration is safe.

3. 03
   Test a versioned migration

   Exercise upgrade, verification, and recovery against representative data in the production DBMS.

4. 04
   Deploy and observe

   Coordinate compatible application changes, monitor duration and errors, then confirm the intended constraint exists.

Recognize table-design smells early

Independent lab: design a workshops table

Begin with the working table, then strengthen it. Add a required starts_at column using a text timestamp suitable for this SQLite exercise. Insert two more workshops. Return only published workshops with at least 20 seats, ordered by title. Finally, inspect the schema and write one sentence explaining the meaning of a row.

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  format TEXT NOT NULL CHECK (format IN ('online', 'onsite')),
  seats INTEGER NOT NULL CHECK (seats > 0),
  published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1))
);

INSERT INTO workshops
  (workshop_id, title, format, seats, published)
VALUES
  (1, 'Query Reading', 'online', 40, 1),
  (2, 'Schema Design', 'onsite', 18, 1),
  (3, 'Index Preview', 'online', 25, 0);

SELECT workshop_id, title, format, seats
FROM workshops
WHERE published = 1
ORDER BY title;

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

Lesson review

A table is a named contract for one kind of record. Row grain defines what a single record means. Columns name attributes and combine a type with nullability, defaults, and constraints. The schema is the durable blueprint, while rows are changing state and query results are temporary shapes. Explicit inserts document value mappings; metadata catalogs reveal the structure the database actually has; versioned migrations evolve that structure deliberately.

• I can write a one-sentence grain statement before creating a table.
• I can explain the name, type, requiredness, default, and constraints of a column.
• I never rely on row position or an implicit result order.
• I can inspect SQLite table metadata and compare it with the intended DDL.
• I can describe a staged, verifiable schema migration instead of editing production ad hoc.
• I recognize repeating values, numbered columns, mixed grain, vague names, and unsafe derived data as design smells.