A schema defines the shape of stored data: tables, columns, types, and rules. Good table definitions represent the domain accurately, reject invalid values where possible, and remain understandable as an application evolves. The examples use broadly familiar SQL; identity columns, text types, timestamp behavior, and some constraints differ across engines.
Key Takeaways at a Glance:
- •Numeric precision: Use INT/BIGINT for discrete identifiers and DECIMAL(M, D) for currency and financial amounts; avoid FLOAT/DOUBLE due to binary rounding inaccuracies.
- •Text types: VARCHAR(N) stores variable-length strings up to N characters; TEXT / BLOB stores large unstructured payloads off-page.
- •Date & Time: TIMESTAMP stores UTC epoch timestamps (timezone-aware); DATETIME stores calendar literal timestamps without timezone offsets.
- •NULLability: Always explicitly declare 'NOT NULL' unless the domain rule explicitly permits missing information.
In Simple Words (The Mental Model)
Choosing data types is like buying storage containers for your workshop. If you put screws in a giant cardboard box, they roll around and get lost. If you store money as FLOAT, 0.1 + 0.2 becomes 0.30000000000000004! Using DECIMAL guarantees exact penny-accurate financial math.
Real-World Analogy
Think of an ice cube tray vs a swimming pool. If a column only holds a tiny number from 1 to 5 (like star rating), using BIGINT is like filling a swimming pool to hold one ice cube (wastes 8 bytes per row on disk). Choosing TINYINT uses 1 byte, allowing 8x more rows per database cache page.