Designing tables

The structure of a database is harder to change than code: the tables already hold data. So tables deserve thought in advance — which columns are needed, of what type, how the tables are related and which fields will be searched.

Column types

A type is the first protection of data: a word cannot get into an INT column, nor a non-existent date into DATE.

The primary key and constraints

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

One entity — one table

Author data is not repeated in every post. The author is stored once, and a post refers to them by identifier:

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

If the author changes their name, one row is edited rather than all their posts.

The foreign key

FOREIGN KEY does not let you create a post by a non-existent author. ON DELETE CASCADE deletes posts together with the author. Let us see it in action:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('PRAGMA foreign_keys = ON');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');
$pdo->exec('CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    title TEXT NOT NULL
)');
 
$pdo->exec("INSERT INTO users (email) VALUES ('anna@example.com')");
 
try {
    $pdo->exec("INSERT INTO posts (user_id, title) VALUES (99, 'Post')");
} catch (PDOException $e) {
    echo 'The database rejected the record: there is no author 99';
}

The database catches the error itself — even if the code forgot the check.

Many to many

A post has several tags, a tag has many posts. Such a relationship is stored in a third table with pairs of identifiers:

CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);
 
CREATE TABLE post_tag (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
);

The composite primary key stops one tag from being attached to one post twice.

Indexes

Without an index the database scans the whole table to search. An index is something like the alphabetical index of a book:

CREATE INDEX posts_user_id_index ON posts (user_id);
CREATE INDEX posts_created_at_index ON posts (created_at);

Indexes go on columns used for searching, ordering and joining tables. A primary key and UNIQUE create an index by themselves. Extra indexes hurt: each one slows down inserting and changing rows.

Common mistake

Money must not be stored in FLOAT or DOUBLE: these types are approximate, and the sum 0.1 + 0.2 gives 0.30000000000000004. Use DECIMAL(10, 2) or store a whole number of cents in INT. A second trouble is several values in one column, such as comma-separated tags: such a field cannot be searched properly. Lists get a table of their own.