Design-Patterns

Good schema design is not about theory. It is about patterns that solve real problems — audit trails, multi-tenancy, concurrent updates, and data history. These patterns work with any relational database. Soft Deletes Hard deleting rows permanently removes data. Soft deletes mark rows as deleted without removing them — useful for audit trails, undo functionality, and regulatory compliance. ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ; -- Soft delete UPDATE users SET deleted_at = NOW() WHERE id = 42; -- Query active users only SELECT * FROM users WHERE deleted_at IS NULL; -- Include deleted users SELECT * FROM users; -- Restore UPDATE users SET deleted_at = NULL WHERE id = 42; Create a partial index for performance on active records: ...