Choosing Data Types Wisely

Choose the narrowest type that holds the entire range of values you will ever store. That single rule drives most of the decision, because a narrow column is smaller on disk, faster to compare, and able to fit more rows per index page — which means the index is smaller and lookups are faster.

CREATE TABLE type_demo (
  id INTEGER PRIMARY KEY,
  price_cents INTEGER NOT NULL,
  ratio REAL NOT NULL,
  sku VARCHAR(40) NOT NULL,
  is_active INTEGER NOT NULL DEFAULT 1,
  recorded_at TEXT NOT NULL
);

INSERT INTO type_demo (price_cents, ratio, sku, is_active, recorded_at)
VALUES (1999, 0.75, 'KB-01', 1, '2026-03-01');

SELECT typeof(price_cents) AS price_type,
       typeof(ratio) AS ratio_type,
       typeof(sku) AS sku_type,
       typeof(is_active) AS flag_type,
       typeof(recorded_at) AS date_type
FROM type_demo;

Five types come back: integer, real, text, integer and text. SQLite has a dynamic type system with five storage classes — NULL, INTEGER, REAL, TEXT, BLOB — and it converts a value to the storage class that fits it, so a column declared VARCHAR holds text and a column declared INTEGER holds an integer. Other engines are stricter: PostgreSQL will reject a string in an integer column rather than converting it.

The practical choices. Use INTEGER for counts, identifiers and money in cents. Use REAL for genuinely fractional measurements, with the understanding that it is a binary approximation. Use a fixed-precision DECIMAL when the value must be exact and fractional — the price of a bond, a tax rate that has to reconcile to the penny — and never for money on a system that also needs to sum quickly. Use TEXT for names, codes and anything you do not control, and a native DATE or TIMESTAMP type if your engine has one, rather than text, so that comparisons and date functions behave.

Two things to avoid. Do not store a number in a text column: it sorts as a string, so '10' comes before '9', and it bloats the index. And do not use a floating point type for money, because 0.1 has no exact binary representation and a sum of prices drifts. Store cents as an integer and convert only for display.

One more consideration that is often skipped: leave room to grow. A TINYINT that holds a status code is perfect today and painful in two years when you need a hundred status codes, because widening it later means a table rewrite. Where a value is small but might grow — a country code, a status, a type flag — a smallint or a plain integer is usually the better trade.

Store the amount as an integer number of cents, name the column so the unit is obvious, and convert to a decimal only for display.

CREATE TABLE invoices (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  issued_at TEXT NOT NULL DEFAULT (datetime('now')),
  CONSTRAINT chk_invoices_amount CHECK (amount_cents >= 0)
);

INSERT INTO invoices (id, customer_id, amount_cents) VALUES
  (1, 1, 1999),
  (2, 1, 7500),
  (3, 2, 12350),
  (4, 2, 999);

SELECT id,
       amount_cents,
       ROUND(CAST(amount_cents AS REAL) / 100.0, 2) AS amount_display,
       SUM(amount_cents) OVER () AS invoice_total_cents
FROM invoices
ORDER BY id;

Four rows come back, showing 19.99, 75.00, 123.50 and 9.99, with a total of 31648 cents across the four invoices. The stored values are integers throughout, so the total is exact — there is no floating-point drift anywhere in the arithmetic.

Why the column name matters is not cosmetic. A column called amount holding 1999 invites somebody to write 1999.00 in a report and to divide by 100 in one place and not another. A column called amount_cents states the unit at every point of use, so the conversion is always a deliberate, visible step.

The CHECK constraint is there for a business reason: a negative invoice is either a refund or a data error, and in most systems it should be represented as a separate credit rather than a negative sale. Deciding that at the schema level is much cheaper than discovering it in a reconciliation.

One trade-off to name. Storing cents means you cannot do arithmetic in the currency's own unit without converting, and multi-currency becomes slightly more involved — a currency column plus an amount in that currency's minor units, with the minor-unit factor varying by currency, which is a real complication. The alternative, a fixed-precision DECIMAL column, handles that more naturally but is slower to sum and cannot be indexed as efficiently in some engines. For a single-currency system, integer cents is the simpler and safer answer.