How do I choose the right data type for a column?
You pick the narrowest type that holds the value, and you never use a floating point number for money.
CREATE TABLE type_demo (
id INTEGER PRIMARY KEY,
price_cents INTEGER NOT NULL,
ratio REAL NOT NULL,
label VARCHAR(40) NOT NULL,
is_open INTEGER NOT NULL,
created_on TEXT NOT NULL,
payload BLOB
);
So here is how the choices go.
For a whole number, use INTEGER unless the value is genuinely larger than about two billion, in which case use BIGINT. For money, use INTEGER that holds the small unit, so that 1050 means ten pounds fifty, and never use REAL. For a number with a fraction, use REAL only for a measurement such as a ratio, and use DECIMAL when the number has to be exact.
For text, use VARCHAR with a length. The length is not a limit that you have to reach, it is a hint that tells the engine how much space to set aside, and a length of two hundred on a column that holds a country name is a hundred and seventy characters wasted on every row.
For a date and a time, use the engine's own date type rather than text. The date type sorts correctly, it can be compared with a range, and the engine can use an index on it.
And there is one that surprises people. A boolean is a number in most engines, with a rule that says it can only be zero or one. So the column is an integer with a check rule on it.
is_open INTEGER NOT NULL DEFAULT 1 CHECK (is_open IN (0, 1))
The next question in this topic puts the money one into a table.