Design Patterns

Proven patterns

The status/state pattern

ORDER_STATUS (id, label)\nORDER (id, status_id, ...)\n-- statuses: pending, paid, shipped, cancelled

The audit trail pattern

PAYMENT_LOG\n├─ id\n├─ payment_id\n├─ action       (created, refunded, failed)\n├─ amount\n├─ actor_user_id\n├─ created_at

Financial systems must record history. An append-only log table never deletes — it explains every state change.

The soft delete pattern

courses.deleted_at DATETIME NULL\n-- queries filter WHERE deleted_at IS NULL

The polymorphic reference (use sparingly)

notifications.subject_type VARCHAR(20)\nnotifications.subject_id INT

When to denormalise

• A computed total shown on every list page
• Counts that change rarely (course enrollment count)
• Read-heavy reporting tables

Normalise by default; denormalise deliberately for a measured performance win.

Tip: Add an audit log whenever money, permissions or important state are involved. Future you (and auditors) will thank you.