Getting columns right.
WHY IT MATTERS
Types affect storage, speed, index size and whether invalid data is possible.
WHAT TO CHOOSE FOR WHOLE NUMBERS
The smallest integer type that fits the range.
WHY NOT ALWAYS THE LARGEST
Every row pays the cost, and indexes grow with it.
WHAT TO NEVER USE FOR MONEY
Floating point.
WHY
It cannot represent many decimal values exactly, and errors accumulate.
WHAT TO USE INSTEAD
A fixed-point decimal type, or integers of the smallest unit.
WHAT TO CHOOSE FOR TEXT
A variable-length type with a sensible limit.
WHAT A FIXED-LENGTH TYPE SUITS
Values genuinely always the same length.
WHY LIMITS MATTER
They document intent and prevent absurd data.
WHAT TO USE FOR DATES AND TIMES
A proper date or timestamp type, never text.
WHY
Text dates cannot be compared, sorted or filtered correctly.
WHAT TO STORE TIMESTAMPS IN
A single standard, converting at presentation.
WHAT TO USE FOR TRUE AND FALSE
A small integer or boolean type.
WHAT TO BE CAREFUL WITH
Very large text columns in frequently read tables Storing files in the database
WHY THAT SECOND POINT
It bloats backups and memory for data better held on disk.
WHAT TO STORE INSTEAD
A path or identifier.
