Data Modeling Patterns
Good data models make everything easier — queries are simpler, performance is better, and bugs are fewer. Bad data models create problems that haunt you for years. Learning common patterns helps you avoid reinventing solutions to problems others have already solved.
Soft Deletes
Sometimes you need to "delete" data while keeping it recoverable. Instead of actually removing rows, mark them as deleted:
-- Add a deletion timestamp
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;
-- "Delete" a user
UPDATE users SET deleted_at = NOW() WHERE id = 123;
-- Query only active users
SELECT * FROM users WHERE deleted_at IS NULL;
This pattern preserves data for auditing, allows recovery from accidental deletions, and maintains referential integrity. The trade-off is that every query must filter out deleted records — consider creating a view for convenience.
Audit Trails
When you need to track who changed what and when, an audit log captures that history:
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(50),
record_id INTEGER,
action VARCHAR(10), -- INSERT, UPDATE, DELETE
old_data JSONB,
new_data JSONB,
changed_by INTEGER,
changed_at TIMESTAMP DEFAULT NOW()
);
Database triggers can populate this automatically, or your application can write audit entries alongside changes. This is essential for compliance, debugging, and understanding how data evolved.
Polymorphic Associations
Sometimes one table needs to reference multiple other tables. Comments might apply to posts, photos, and videos:
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
commentable_type VARCHAR(50), -- 'post', 'photo', 'video'
commentable_id INTEGER,
body TEXT
);
This pattern is flexible but sacrifices foreign key constraints. You can't have the database enforce that commentable_id points to a valid record. Use it when flexibility outweighs that trade-off.
Temporal Data
When you need to track values over time — prices, statuses, configurations — store validity periods:
CREATE TABLE prices (
product_id INTEGER,
price DECIMAL,
valid_from TIMESTAMP,
valid_to TIMESTAMP
);
-- Get current price
SELECT price FROM prices
WHERE product_id = 123
AND valid_from <= NOW()
AND (valid_to IS NULL OR valid_to > NOW());
This lets you query historical data ("what was the price last month?") and schedule future changes.
Design for Your Access Patterns
The best data model depends on how you'll query the data. A model optimized for writes might be terrible for reads. Think about your most common queries first, then design accordingly.