Choose column data types

A column type describes the values the column is meant to hold. It also affects comparison, sorting, validation, storage, and calculations.

For an orders table, the choices might look like this:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_email VARCHAR(320) NOT NULL,
  item_count INTEGER NOT NULL,
  total NUMERIC(10, 2) NOT NULL,
  paid BOOLEAN NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Use integer types for counts and identifiers. Use an exact decimal or numeric type for money. Binary floating-point types are useful for measurements, but values such as 0.1 cannot always be represented exactly, which is a bad property for account balances.

Use date and time types when the database needs to order, compare, or calculate with time. Decide whether a value is an instant, a calendar date, or a local wall-clock time. Those are different facts.

Not every number is numeric data. Phone numbers, postal codes, and product codes can contain leading zeroes or punctuation and are not added together. Store them as text.

The declared type is only part of the contract. INTEGER still accepts -4, so a quantity also needs a CHECK constraint when negative values are invalid. A timestamp can be valid syntax while representing the wrong time zone.

Changing a type later can require scanning, converting, or locking a large table. Choose from real values and queries, not from the shortest-looking type name.

Exact names, ranges, timestamp behavior, and automatic conversions differ between SQLite, PostgreSQL, and MySQL. Check the documentation for the database you will run.

Your action is to choose types for a price, birth date, phone number, inventory count, and creation instant. Write one sentence explaining each choice.