Starting correctly.
WHAT TO DECIDE FIRST
Which database, and which version.
WHAT TO CREATE
A database with the correct character set and collation An application account with minimum privileges A separate account for migrations
WHY THE CHARACTER SET AT CREATION
Changing it later is painful and risks corruption.
WHAT TO SET UP IMMEDIATELY
Backups A restore test Monitoring The slow query log
WHY BACKUPS BEFORE ANYTHING ELSE
The application will hold real data sooner than you expect.
WHAT TO ESTABLISH IN THE SCHEMA
Primary keys on every table Foreign keys where relationships exist Sensible types and lengths Constraints for anything that must be true
WHAT TO AGREE AS CONVENTIONS
Naming of tables and columns Singular or plural table names How timestamps are recorded
WHY CONVENTIONS EARLY
Inconsistency is permanent once data exists.
WHAT TO ADD TO EVERY TABLE
Created and updated timestamps.
WHY
They are needed constantly and cannot be reconstructed later.
WHAT TO PLAN
How migrations are managed and applied.
WHAT TO DOCUMENT
The decisions, and why.
