Lesson 3 — Structure

Tables

Tables are the fundamental structure in a relational database. Every design decision — column types, constraints, naming — affects data integrity and query performance.

Anatomy of a Table

A table has a schema (its definition — column names and types) and data (the rows). The schema is defined once with CREATE TABLE; the data changes constantly via INSERT, UPDATE, and DELETE. Getting the schema right is critical — changing it in production (with ALTER TABLE) is disruptive.

SQL Data Types

Choose the most specific type that fits your data. The wrong type causes silent bugs, wasted storage, and poor performance.

SQL — Data types 
-- INTEGER types:
--   TINYINT    — -128 to 127 (or 0-255 unsigned)
--   SMALLINT   — ±32,767
--   INT        — ±2.1 billion     (most common)
--   BIGINT     — ±9.2 quintillion (IDs, timestamps)

-- DECIMAL types:
--   DECIMAL(p, s) — exact decimal with p total digits, s after decimal
--   Use for MONEY, PRICES, RATES — never FLOAT for financial data
--   FLOAT / DOUBLE — approximate — use only for scientific calculations

-- STRING types:
--   VARCHAR(n)  — variable length up to n characters
--   CHAR(n)     — fixed length, padded with spaces (use for fixed codes)
--   TEXT        — unlimited length (no index on full TEXT in MySQL)

-- DATE/TIME types:
--   DATE        — calendar date: 2025-04-01
--   TIME        — time of day: 14:30:00
--   DATETIME    — date + time: 2025-04-01 14:30:00
--   TIMESTAMP   — same but auto-converts to UTC

-- BOOLEAN:
--   BOOLEAN / BIT(1) — true/false (MySQL: TINYINT(1) in practice)
SQL — CREATE TABLE example 
-- Complete CREATE TABLE example:
CREATE TABLE learners (
    learner_id     INT           NOT NULL AUTO_INCREMENT,  -- surrogate PK
    full_name      VARCHAR(100)  NOT NULL,
    id_number      VARCHAR(13),                           -- SA ID: 13 digits
    age            SMALLINT,
    city           VARCHAR(50),
    province       VARCHAR(50),
    course         VARCHAR(50)   NOT NULL DEFAULT 'Undecided',
    mark           DECIMAL(5, 2),  -- 999.99 max — 5 digits total, 2 after decimal
    email          VARCHAR(150)  UNIQUE,
    phone          VARCHAR(15),
    is_active      BOOLEAN       NOT NULL DEFAULT TRUE,
    enrolment_date DATE,
    created_at     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT pk_learners PRIMARY KEY (learner_id)
);

Column Constraints Summary

  • NOT NULL — column must have a value. The most important constraint for required fields.
  • UNIQUE — no two rows may have the same value. Email addresses, ID numbers.
  • DEFAULT value — used if no value is provided on INSERT.
  • CHECK (condition) — rejects values that don't satisfy the expression.
  • PRIMARY KEY — combines NOT NULL and UNIQUE; identifies each row.
  • FOREIGN KEY — references a primary key in another table.

Practice Task

Your Turn

Write a CREATE TABLE statement for a courses table with: course_id (INT auto-increment PK), title (VARCHAR 100, NOT NULL), category (VARCHAR 50), duration_weeks (TINYINT, CHECK 1-52), max_learners (SMALLINT, DEFAULT 30), facilitator_id (INT, will be FK in a later lesson), start_date (DATE), is_active (BOOLEAN, DEFAULT TRUE). Use appropriate types for each.

Common Mistakes

  • Using FLOAT for money — always use DECIMAL for financial values.
  • VARCHAR too small — allow room: names at least 100 chars, addresses at least 200.
  • Missing NOT NULL on required fields — NULL values create query ambiguity.
  • No primary key — every table must have one to ensure rows are uniquely identifiable.

Professional Tip

Always choose the smallest type that fits your data. TINYINT for age, SMALLINT for year, INT for general IDs. This reduces storage and improves index performance.

Mini Quiz

Which SQL type is best for storing financial amounts?