Schemas, CREATE TABLE & Type Design — SQL Course

What you'll learn

Create namespaces and tables with meaningful names, precise PostgreSQL types, defaults, identity columns, and timestamps. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Schemas to a realistic data question
Apply CREATE TABLE to a realistic data question
Apply Identity columns to a realistic data question
Apply Type precision to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Schema	A namespace for database objects	Use it to separate ownership and object groups, not as tenant isolation by default
Identity column	A standard generated numeric value	Prefer GENERATED over manual sequence plumbing
Default	A value supplied when a column is omitted	Use for stable database-owned defaults, not hidden business workflows

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the table-definition contract question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Create a precise product table

Types, defaults, and generated identity encode meaning before constraints are added.

create_products.sql

CREATE SCHEMA IF NOT EXISTS inventory;
CREATE TABLE inventory.products (
  id bigint GENERATED ALWAYS AS IDENTITY,
  sku text NOT NULL,
  name text NOT NULL,
  price numeric(12,2) NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Choosing varchar lengths, numeric precision, or timestamps by habit rather than domain meaning creates arbitrary limits and subtle data loss.

Independent workshop

Build a review-ready table-definition contract lab against the course commerce dataset.

Your finished workshop must include:

Schemas
CREATE TABLE
Identity columns
Type precision
Defaults
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Schema: Use it to separate ownership and object groups, not as tenant isolation by default
Identity column: Prefer GENERATED over manual sequence plumbing
Default: Use for stable database-owned defaults, not hidden business workflows

Quick check

1. Which rule best applies to Schema?

2. Which rule best applies to Identity column?

3. Which rule best applies to Default?

Next: Keys, Constraints & Referential Integrity