SQL Schema Best Practices
Getting your table schema right at the start saves hours of painful migrations later. Here's what matters most.
Always set a Primary Key
Every table needs a PK. Use INT AUTO_INCREMENT for MySQL/SQL Server, SERIAL or UUID for PostgreSQL. Composite PKs work for junction tables.
NOT NULL vs nullable
Default to NOT NULL and add a DEFAULT value. Nullable columns signal "this data may not exist" — be intentional, not lazy. Avoids NULL-check bugs everywhere.
VARCHAR length matters
Don't default to VARCHAR(255) everywhere. Emails fit in 254, slugs in 100, names in 120. Tight types = smaller indexes = faster queries.
Indexes on Foreign Keys
MySQL doesn't auto-index FK columns. Always add an index on columns you JOIN or WHERE on. Over-indexing hurts writes; under-indexing kills reads.
Timestamps pattern
Add created_at and updated_at to every table. Use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP (MySQL) so your app never has to think about it.
InnoDB vs MyISAM
Always use InnoDB for MySQL. It supports foreign keys, transactions, and row-level locking. MyISAM is legacy — only for very specific full-text search cases on old MySQL versions.