Tables and relationships.
WHAT TO ESTABLISH
What things exist What each holds How they relate
WHAT TO NAME TABLES
After what they contain, plurally, by convention.
WHAT EVERY TABLE NEEDS
A primary identifier.
WHAT RELATIONSHIPS LOOK LIKE
One record belonging to another, via a reference Many-to-many, via an intermediate table
WHAT TO ADD
Foreign key constraints, so invalid references cannot exist.
WHY
The database enforces what your code might forget.
WHAT TO INDEX
Anything you filter, sort or join by, frequently.
WHY
Without an index, the database examines every row.
WHAT NOT TO OVER-INDEX
Every column, which slows writing.
WHAT TYPES TO CHOOSE
The smallest that accommodates the data Exact decimal types for money, never floating point
THAT LAST POINT
Floating point arithmetic cannot represent some values exactly, which is unacceptable for money.
WHAT TO STORE FOR DATES
A type carrying zone information, consistently.
