Lesson 8: Model the rules in the schema
Choose types that represent the domain. Use numeric types for exact money, timestamps with time zones for global events, and constraints for invariants that every writer must obey.
CREATE TABLE customers (
customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(customer_id),
total numeric(12,2) NOT NULL CHECK (total >= 0)
);Schema building blocks
11 concepts shown
| Feature | Purpose | Example |
|---|---|---|
| CREATE TABLE | Defines columns and constraints. | CREATE TABLE products (id bigint, name text); |
| NOT NULL | Requires a value. | email text NOT NULL |
| PRIMARY KEY | Uniquely identifies each row. | customer_id bigint PRIMARY KEY |
| FOREIGN KEY | Enforces a relationship to a referenced key. | customer_id REFERENCES customers(customer_id) |
| UNIQUE | Prevents duplicate values or combinations. | UNIQUE (tenant_id, external_id) |
| CHECK | Enforces a Boolean rule for each row. | CHECK (quantity >= 0) |
| DEFAULT | Provides a value when none is supplied. | created_at timestamptz DEFAULT CURRENT_TIMESTAMP |
| IDENTITY | Generates numeric key values in PostgreSQL. | id bigint GENERATED ALWAYS AS IDENTITY |
| ALTER TABLE | Changes an existing table definition. | ALTER TABLE products ADD COLUMN active boolean NOT NULL DEFAULT true; |
| DROP TABLE | Removes a table and its data; destructive. | DROP TABLE staging_data; |
| VIEW | Stores a reusable query definition. | CREATE VIEW paid_orders AS SELECT * FROM orders WHERE status='paid'; |
Choose types and relationships
Use numeric(p,s) for exact decimal quantities such as money, date for calendar dates, timestamptz for instants, and jsonb for flexible PostgreSQL JSON. Normalize repeated entities and define delete/update behavior on foreign keys deliberately.
Progress stays in this browser.