Bad Code Can Be Refactored; Bad Schemas Haunt You Forever
You can easily rewrite a React component in an afternoon. But if you design a flawed relational database schema with missing foreign keys and unindexed lookup columns, migrating millions of production rows while keeping the app online is a painful engineering nightmare.
4 Golden Rules of Relational Database Design:
1Always Use UUIDs or BigInt for Primary Keys:
Standard 32-bit integers max out at 2.14 billion rows, which high-volume event logs or audit tables can hit surprisingly fast. Use UUIDv4 or UUIDv7 (time-sortable) to allow safe client-side ID generation and distributed writes.
2Index Foreign Keys and Frequent Query Filters:
Whenever you declare a foreign key (
customer_id, hospital_id), immediately add an index:sqlKorvith Code Snippet
CREATE INDEX idx_appointments_patient_date ON appointments (patient_id, appointment_date DESC);Without this index, every patient history query performs a brutal full-table sequential scan.
3Beware the N+1 Query Trap:
When fetching 50 orders, don't execute 1 query for orders and then 50 separate queries in a loop to fetch each customer name. Use SQL
JOIN statements or batch loaders to retrieve everything in a single round-trip.4Soft Deletes vs Hard Deletes:
For mission-critical records (financial transactions, patient history), never execute
DELETE FROM table. Use a deleted_at TIMESTAMP WITH TIME ZONE column to preserve full auditability.