Good database design is the foundation of a scalable application. Here are patterns that solve common problems.
Normalization vs Denormalization#
1-- Normalized: Data integrity, less redundancy
2-- Good for: OLTP, frequently updated data
3
4CREATE TABLE users (
5 id UUID PRIMARY KEY,
6 email VARCHAR(255) UNIQUE NOT NULL,
7 name VARCHAR(100) NOT NULL
8);
9
10CREATE TABLE orders (
11 id UUID PRIMARY KEY,
12 user_id UUID REFERENCES users(id),
13 total DECIMAL(10,2) NOT NULL,
14 created_at TIMESTAMP DEFAULT NOW()
15);
16
17CREATE TABLE order_items (
18 id UUID PRIMARY KEY,
19 order_id UUID REFERENCES orders(id),
20 product_id UUID REFERENCES products(id),
21 quantity INT NOT NULL,
22 price DECIMAL(10,2) NOT NULL
23);
24
25-- Denormalized: Faster reads, data redundancy
26-- Good for: Read-heavy, analytics, caching
27
28CREATE TABLE order_summary (
29 id UUID PRIMARY KEY,
30 user_id UUID,
31 user_name VARCHAR(100), -- Denormalized
32 user_email VARCHAR(255), -- Denormalized
33 total DECIMAL(10,2),
34 item_count INT, -- Computed
35 created_at TIMESTAMP
36);Soft Deletes#
1-- Soft delete with timestamp
2CREATE TABLE posts (
3 id UUID PRIMARY KEY,
4 title VARCHAR(255) NOT NULL,
5 content TEXT,
6 created_at TIMESTAMP DEFAULT NOW(),
7 updated_at TIMESTAMP DEFAULT NOW(),
8 deleted_at TIMESTAMP NULL -- NULL = not deleted
9);
10
11-- Query active records
12SELECT * FROM posts WHERE deleted_at IS NULL;
13
14-- Query deleted records
15SELECT * FROM posts WHERE deleted_at IS NOT NULL;
16
17-- Soft delete
18UPDATE posts SET deleted_at = NOW() WHERE id = ?;
19
20-- Restore
21UPDATE posts SET deleted_at = NULL WHERE id = ?;
22
23-- Prisma implementation
24model Post {
25 id String @id @default(cuid())
26 title String
27 deletedAt DateTime?
28
29 @@index([deletedAt])
30}
31
32// Middleware for automatic filtering
33prisma.$use(async (params, next) => {
34 if (params.model === 'Post' && params.action === 'findMany') {
35 params.args.where = { ...params.args.where, deletedAt: null };
36 }
37 return next(params);
38});Polymorphic Associations#
1-- Option 1: Single Table Inheritance (STI)
2CREATE TABLE notifications (
3 id UUID PRIMARY KEY,
4 type VARCHAR(50) NOT NULL, -- 'email', 'sms', 'push'
5 user_id UUID REFERENCES users(id),
6 message TEXT NOT NULL,
7 -- Email-specific
8 email_subject VARCHAR(255),
9 -- SMS-specific
10 phone_number VARCHAR(20),
11 -- Push-specific
12 device_token VARCHAR(255),
13 created_at TIMESTAMP DEFAULT NOW()
14);
15
16-- Option 2: Class Table Inheritance
17CREATE TABLE notifications (
18 id UUID PRIMARY KEY,
19 user_id UUID REFERENCES users(id),
20 message TEXT NOT NULL,
21 created_at TIMESTAMP DEFAULT NOW()
22);
23
24CREATE TABLE email_notifications (
25 id UUID PRIMARY KEY REFERENCES notifications(id),
26 subject VARCHAR(255),
27 recipient_email VARCHAR(255)
28);
29
30CREATE TABLE sms_notifications (
31 id UUID PRIMARY KEY REFERENCES notifications(id),
32 phone_number VARCHAR(20)
33);
34
35-- Option 3: Polymorphic with reference
36CREATE TABLE comments (
37 id UUID PRIMARY KEY,
38 content TEXT NOT NULL,
39 commentable_type VARCHAR(50) NOT NULL, -- 'Post', 'Photo', 'Video'
40 commentable_id UUID NOT NULL,
41 created_at TIMESTAMP DEFAULT NOW()
42);
43
44-- Index for polymorphic lookups
45CREATE INDEX idx_comments_polymorphic
46ON comments(commentable_type, commentable_id);Temporal Data (History)#
1-- Audit trail with triggers
2CREATE TABLE users (
3 id UUID PRIMARY KEY,
4 email VARCHAR(255),
5 name VARCHAR(100),
6 updated_at TIMESTAMP DEFAULT NOW()
7);
8
9CREATE TABLE users_history (
10 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
11 user_id UUID NOT NULL,
12 email VARCHAR(255),
13 name VARCHAR(100),
14 changed_at TIMESTAMP DEFAULT NOW(),
15 changed_by UUID,
16 operation VARCHAR(10) -- 'INSERT', 'UPDATE', 'DELETE'
17);
18
19-- Trigger function
20CREATE OR REPLACE FUNCTION audit_users()
21RETURNS TRIGGER AS $$
22BEGIN
23 IF TG_OP = 'DELETE' THEN
24 INSERT INTO users_history (user_id, email, name, operation)
25 VALUES (OLD.id, OLD.email, OLD.name, 'DELETE');
26 RETURN OLD;
27 ELSE
28 INSERT INTO users_history (user_id, email, name, operation)
29 VALUES (NEW.id, NEW.email, NEW.name, TG_OP);
30 RETURN NEW;
31 END IF;
32END;
33$$ LANGUAGE plpgsql;
34
35CREATE TRIGGER users_audit_trigger
36AFTER INSERT OR UPDATE OR DELETE ON users
37FOR EACH ROW EXECUTE FUNCTION audit_users();
38
39-- Bitemporal: Track valid time AND transaction time
40CREATE TABLE prices (
41 id UUID PRIMARY KEY,
42 product_id UUID,
43 amount DECIMAL(10,2),
44 valid_from DATE NOT NULL,
45 valid_to DATE,
46 recorded_at TIMESTAMP DEFAULT NOW(),
47 superseded_at TIMESTAMP
48);Tree Structures#
1-- Adjacency List (simple, recursive queries needed)
2CREATE TABLE categories (
3 id UUID PRIMARY KEY,
4 name VARCHAR(100),
5 parent_id UUID REFERENCES categories(id)
6);
7
8-- Get descendants (PostgreSQL recursive CTE)
9WITH RECURSIVE descendants AS (
10 SELECT * FROM categories WHERE id = ?
11 UNION ALL
12 SELECT c.* FROM categories c
13 JOIN descendants d ON c.parent_id = d.id
14)
15SELECT * FROM descendants;
16
17-- Materialized Path (fast reads)
18CREATE TABLE categories (
19 id UUID PRIMARY KEY,
20 name VARCHAR(100),
21 path VARCHAR(1000) -- '1/5/12/45'
22);
23
24-- Get all descendants
25SELECT * FROM categories WHERE path LIKE '1/5/%';
26
27-- Get ancestors
28SELECT * FROM categories
29WHERE '1/5/12/45' LIKE path || '%'
30ORDER BY LENGTH(path);
31
32-- Nested Sets (fast subtree queries, slow updates)
33CREATE TABLE categories (
34 id UUID PRIMARY KEY,
35 name VARCHAR(100),
36 lft INT NOT NULL,
37 rgt INT NOT NULL
38);
39
40-- Get all descendants
41SELECT * FROM categories
42WHERE lft > parent.lft AND rgt < parent.rgt;Many-to-Many with Metadata#
1-- Join table with additional data
2CREATE TABLE product_categories (
3 product_id UUID REFERENCES products(id),
4 category_id UUID REFERENCES categories(id),
5 is_primary BOOLEAN DEFAULT FALSE,
6 sort_order INT DEFAULT 0,
7 added_at TIMESTAMP DEFAULT NOW(),
8 added_by UUID REFERENCES users(id),
9 PRIMARY KEY (product_id, category_id)
10);
11
12-- Ensure only one primary category per product
13CREATE UNIQUE INDEX idx_primary_category
14ON product_categories(product_id)
15WHERE is_primary = TRUE;Tagging Pattern#
1CREATE TABLE tags (
2 id UUID PRIMARY KEY,
3 name VARCHAR(100) UNIQUE NOT NULL,
4 slug VARCHAR(100) UNIQUE NOT NULL
5);
6
7CREATE TABLE post_tags (
8 post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
9 tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
10 PRIMARY KEY (post_id, tag_id)
11);
12
13-- Find posts with ALL specified tags
14SELECT p.*
15FROM posts p
16WHERE (
17 SELECT COUNT(*) FROM post_tags pt
18 WHERE pt.post_id = p.id
19 AND pt.tag_id IN (?, ?, ?)
20) = 3;
21
22-- Find posts with ANY specified tags
23SELECT DISTINCT p.*
24FROM posts p
25JOIN post_tags pt ON p.id = pt.post_id
26WHERE pt.tag_id IN (?, ?, ?);
27
28-- PostgreSQL: Use array for simpler queries
29CREATE TABLE posts (
30 id UUID PRIMARY KEY,
31 title VARCHAR(255),
32 tags TEXT[] DEFAULT '{}'
33);
34
35SELECT * FROM posts WHERE 'javascript' = ANY(tags);
36SELECT * FROM posts WHERE tags @> ARRAY['javascript', 'react'];JSON/JSONB Columns#
1-- Flexible schema for metadata
2CREATE TABLE products (
3 id UUID PRIMARY KEY,
4 name VARCHAR(255) NOT NULL,
5 price DECIMAL(10,2) NOT NULL,
6 attributes JSONB DEFAULT '{}'
7);
8
9-- Index for JSON queries
10CREATE INDEX idx_products_attributes ON products USING GIN(attributes);
11
12-- Query JSON
13SELECT * FROM products WHERE attributes->>'color' = 'red';
14SELECT * FROM products WHERE attributes @> '{"size": "large"}';
15SELECT * FROM products WHERE attributes ? 'warranty';
16
17-- Update JSON
18UPDATE products
19SET attributes = attributes || '{"featured": true}'
20WHERE id = ?;
21
22-- Remove key
23UPDATE products
24SET attributes = attributes - 'temp_field'
25WHERE id = ?;Best Practices#
Design:
✓ Normalize first, denormalize when needed
✓ Use appropriate data types
✓ Add indexes for query patterns
✓ Plan for growth
Integrity:
✓ Use foreign keys
✓ Add constraints (NOT NULL, CHECK)
✓ Consider soft deletes for audit
✓ Use transactions
Performance:
✓ Index foreign keys
✓ Avoid over-indexing
✓ Partition large tables
✓ Monitor query plans
Conclusion#
Choose database patterns based on your access patterns. Normalize for data integrity, denormalize for read performance. Use appropriate patterns for hierarchies, temporal data, and flexible schemas. Always index based on actual query patterns and monitor performance.