Database Schema Decisions That Come Back to Haunt You

I have inherited four production databases. One had a table called data. Another had columns called info1, info2, info3. A third stored timestamps as VARCHAR. All of them caused real problems that were completely avoidable at design time.

Schema decisions made in week one shape every query, join, and migration that comes after.

Naming Conventions: Be Boring, Be Consistent

-- Good
CREATE TABLE users (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name   VARCHAR(100) NOT NULL,
    email        VARCHAR(255) NOT NULL UNIQUE,
    is_active    TINYINT(1) NOT NULL DEFAULT 1,
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Bad (all real examples)
CREATE TABLE UsersTBL (
    UserID    INT,
    firstname VARCHAR(50),
    LAST_NAME VARCHAR(50),
    EmailAddr TEXT,
    flag      INT
);

snake_case throughout. Boolean columns prefixed with is_ or has_. Always add created_at and updated_at. No exceptions.

Nullability: NOT NULL Is the Default

Columns should be NOT NULL unless there is a specific reason for nullability. NULL introduces three-value logic in SQL and makes queries more complex:

phone_number VARCHAR(20) NULL,      -- OK: not everyone has one
avatar_url   VARCHAR(500) NULL,     -- OK: might not have uploaded yet
email        VARCHAR(255) NOT NULL, -- Never nullable
created_at   TIMESTAMP NOT NULL     -- Never nullable

Always Define Foreign Key Constraints

// Laravel migration
$table->foreignId('user_id')->constrained()->cascadeOnDelete();

Without foreign key constraints, you can have rows referencing users that do not exist. This causes subtle, hard-to-debug data integrity issues.

JSON Columns: Useful But Overused

JSON columns are for genuinely unstructured or highly variable data. I see them used as an escape hatch to avoid designing a proper schema:

-- Acceptable: event metadata varies wildly by event type
CREATE TABLE audit_logs (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    metadata   JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Bad: if you filter on metadata->>'$.user_id' regularly,
-- that field should be a real indexed column

Soft Deletes: Know the Trade-offs

Soft deletes preserve data instead of removing it. Useful for audit trails. But:

• Every query needs a WHERE deleted_at IS NULL condition
• Unique constraints get complex — two deleted rows with the same email that would conflict if restored
• Tables grow forever — you are never actually deleting anything

My rule: use soft deletes when the data has regulatory or business value worth preserving. Use hard deletes plus a separate audit log table for everything else.

Index Everything You Filter On

$table->index(['user_id', 'status', 'created_at']); // Compound for common queries
$table->unique('slug');
$table->fullText(['title', 'body']); // For text search

Add indexes on all foreign key columns, all WHERE columns, all ORDER BY columns, and all UNIQUE columns. Run EXPLAIN on your slowest queries if you see type: ALL — that means a missing index.