Tables, Schemas, and Types — SQL Basics You Actually Remember

"Think of a table like a class, but without the existential crisis." — your slightly theatrical CS50 TA

You just finished structuring bigger Python programs with classes, iterators, decorators, and typing. Nice! Now imagine you want to persist those class instances reliably, query them fast, and share them with other programs. Welcome to relational databases. This lesson builds on your OOP intuition and turns it into durable, queryable structure: tables, schemas, and types.

What this lesson covers (and why you care)

• What tables are and how they map to classes and objects you already know.
• What schemas are (spoiler: they are namespaces + blueprints).
• Common SQL types and how they compare to Python type hints.
• Practical examples: CREATE TABLE, constraints, and picking types right.

Why care? Because choosing the right schema and types early saves you from weird bugs, broken migrations, and the inevitable 3 a.m. data-restore race.

Tables: classes at rest

Micro explanation

A table is a collection of rows (records) with the same set of columns. Each row is like an instance of a class; each column is like an attribute with a declared type.

Example mapping:

• Python dataclass or class attribute -> Table column
• Instance of class -> Row
• Type hint (int, str, datetime) -> SQL type (INTEGER, TEXT, TIMESTAMP)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Notes:

• SERIAL is a convenient auto-increment integer in many DBs (Postgres has SERIAL/BIGSERIAL).
• PRIMARY KEY ensures uniqueness and fast lookups.
• Constraints like NOT NULL and UNIQUE are your first line of defense against bad data.

Why schemas like this stop you from crying later

Constraints + types let the database catch mistakes before your app does. Instead of silently storing "None" or an unexpected string, the DB says: "nope."

Schemas: namespaces and blueprints

The word 'schema' gets used in two related ways:

1. The structure of a table or database — which columns exist, their types, and relationships. (This is the schema definition.)
2. A namespace inside a database (e.g., Postgres schemas). Think folders within a database where tables live.

Imagine your project as a Python package. A Postgres schema is like a subpackage: finance.users vs analytics.users — same table name, different contexts.

CREATE SCHEMA analytics;
CREATE TABLE analytics.events (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  event_type VARCHAR(50),
  happened_at TIMESTAMP
);

Schemas help avoid naming collisions and let you separate concerns (dev vs prod, analytics vs transactional).

Types: choose wisely (your future self will thank you)

Common SQL types and Python equivalents

Micro explanation

SQL types are stricter than Python's dynamic types. That strictness is good: it prevents nonsense like inserting a JSON blob into a column that should only ever hold integers.

Why use NUMERIC for money? Floating point introduces rounding errors. NUMERIC gives you exact decimals with controlled precision.

Constraints: the database's rules for good behavior

Common constraints:

• PRIMARY KEY — unique identifier for rows
• FOREIGN KEY — enforces relationships across tables
• UNIQUE — no duplicates
• NOT NULL — value must exist
• CHECK — arbitrary condition (e.g., age >= 0)

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total NUMERIC(10,2) NOT NULL CHECK (total >= 0)
);

Why do people keep misunderstanding constraints? Because they think constraints are a performance hit. Mostly false: properly indexed constraints make queries faster and prevent expensive downstream fixes.

Mapping OOP concepts to relational design (practical checklist)

• Class -> Table
• Instance attribute with type hint -> Column with SQL type
• Composition (object contains other objects) -> Foreign keys / join tables
• Iterator over objects -> SQL cursor / result set iteration
• Decorator for validation -> Use DB constraints + application-level validators together

Quick example comparing Python dataclass to SQL table:

# Python
@dataclass
class User:
    id: int
    username: str
    email: str
    created_at: datetime

-- SQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Practical tips and gotchas

• Prefer VARCHAR(n) when you have a reasonable maximum (usernames, codes). Use TEXT for free-form content.
• Use TIMESTAMPTZ for events across timezones. Use DATE for birthdays (no time needed).
• For money, prefer NUMERIC(precision, scale) or store cents as INTEGER.
• Don’t use NULL as a magic value. If you can, prefer explicit values or NOT NULL with defaults.
• Index primary key and foreign key columns for faster joins.

Closing: key takeaways

• Tables are structured sets of rows — think of them like class instances stored on disk.
• Schemas are both the blueprint (what columns exist) and namespaces — like folders in a repo.
• Types and constraints are your best friends — they enforce correctness, reduce bugs, and speed up queries.

Quick summary:

• Model your domain like you model classes in Python, but declare column types early.
• Use constraints to move validation close to the data.
• Choose types intentionally: integers for ids, text for documents, numeric for money, timestamps for events.

Memorable insight: invest time in a good schema now, and your queries will run faster than your future self's regret.

If you want, I can convert a Python dataclass module from your homework into a normalized SQL schema and show the CREATE TABLE statements and example queries. Want me to translate one of your classes?