Database schema design for common patterns — users, roles, posts, tags
Concept
Schema design for common patterns — the recurring structures you'll design again and again in web applications. Knowing the standard schema for these patterns saves hours of design time and avoids common mistakes.
Users and authentication: users table with id, email (unique), password_hash, email_verified_at, created_at. Separate password_resets table. Remember tokens in personal_access_tokens (Sanctum).
Soft deletes: Add deleted_at TIMESTAMP NULL. Never hard-delete. Filter with WHERE deleted_at IS NULL. Laravel adds this automatically with SoftDeletes trait + $table->softDeletes() in migration.
Roles and permissions: Two patterns:
- Simple roles:
role ENUM('admin','editor','user')column on users. Works for 2-4 fixed roles. - Many-to-many (
users ↔ roles ↔ permissions): Flexible. Spatie Laravel Permission implements this.
Posts/articles: posts(id, user_id FK, title, slug UNIQUE, body, status ENUM, published_at, created_at). Slug for URLs. Status for draft/published/archived.
Tags (many-to-many): tags(id, name, slug) + taggables(tag_id, taggable_id, taggable_type) (polymorphic) OR post_tags(post_id, tag_id) (simple pivot).
Hierarchical categories: categories(id, name, slug, parent_id FK self-referential). Recursive CTEs to traverse. Consider lft/rgt (Nested Set Model) for read-heavy trees.
Polymorphic relations: comments(id, body, commentable_id, commentable_type) — one comments table for posts, videos, products. commentable_type = 'App\Models\Post'.
Audit log: audit_logs(id, user_id, event, auditable_id, auditable_type, old_values JSON, new_values JSON, created_at). Never modify, append-only.
Code Example
-- Users and auth
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
email_verified_at TIMESTAMP NULL,
remember_token VARCHAR(100) NULL,
deleted_at TIMESTAMP NULL, -- soft delete
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Roles and permissions (many-to-many)
CREATE TABLE roles (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) UNIQUE);
CREATE TABLE permissions (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) UNIQUE);
CREATE TABLE role_user (role_id INT REFERENCES roles(id), user_id BIGINT REFERENCES users(id), PRIMARY KEY(role_id, user_id));
CREATE TABLE permission_role (permission_id INT REFERENCES permissions(id), role_id INT REFERENCES roles(id), PRIMARY KEY(permission_id, role_id));
-- Posts with tags (polymorphic)
CREATE TABLE posts (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL REFERENCES users(id),
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
body LONGTEXT,
status ENUM('draft','published','archived') DEFAULT 'draft',
published_at TIMESTAMP NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL
);
CREATE INDEX idx_posts_slug ON posts (slug);
CREATE INDEX idx_posts_status ON posts (status, published_at);
CREATE TABLE tags (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), slug VARCHAR(50) UNIQUE);
CREATE TABLE taggables (tag_id INT REFERENCES tags(id), taggable_id BIGINT UNSIGNED, taggable_type VARCHAR(100), PRIMARY KEY(tag_id, taggable_id, taggable_type));
-- Polymorphic comments
CREATE TABLE comments (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL REFERENCES users(id),
body TEXT NOT NULL,
commentable_id BIGINT UNSIGNED NOT NULL,
commentable_type VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_comments_morphable ON comments (commentable_type, commentable_id);
-- Self-referential categories
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
slug VARCHAR(100) UNIQUE NOT NULL,
parent_id INT NULL REFERENCES categories(id),
lft INT NULL,
rgt INT NULL -- Nested Set Model for efficient tree reads
);