Choosing Data Types Without Losing Meaning

Learning outcomes

A technically valid type can still be a poor model. Good type selection begins with the meaning of a value, then accounts for range, precision, units, validation, operations, indexing, portability, and change.

Meaning first, storage second

Ask what the value represents before asking how many bytes it requires. The string 00127 could be a quantity, an identifier, a postal code, or a formatted label. Only one of those meanings supports arithmetic.

Identifiers are labels, not quantities

An identifier distinguishes one entity from another. Even when composed only of digits, it may be text. Leading zeros, fixed formatting, check digits, or external ownership are strong signals.

Use integer keys when compact sequential identity is desirable and exposure is acceptable. Use UUIDs or another distributed identifier when independent systems must generate identifiers without coordination. Do not assume “UUID as text” is always best; many engines provide native UUID types.

Money and exact decimal values

Money requires a currency, amount, rounding policy, and scale. Two common representations are:

• an exact decimal type such as DECIMAL(19,4) in a server database;
• an integer count of a documented minor unit, such as cents, when the currency and precision policy permit it.

CREATE TABLE payment (    payment_id INTEGER PRIMARY KEY,    currency_code TEXT NOT NULL        CHECK (length(currency_code) = 3),    amount_minor INTEGER NOT NULL        CHECK (amount_minor >= 0)) STRICT;INSERT INTO payment (currency_code, amount_minor)VALUES ('EUR', 1299);  -- EUR 12.99SELECT currency_code,       amount_minor,       printf('%.2f', amount_minor / 100.0) AS display_amountFROM payment;

Measurements require units and precision

A numeric column named temperature is incomplete. Does it store Celsius, Fahrenheit, or Kelvin? What sensor precision is meaningful? Are values estimates or calibrated measurements?

Use floating point for scientific or sensor values when approximate representation is acceptable. Use exact decimal or scaled integers when business rules require exact decimal arithmetic. Record units in the column name, metadata, or a constrained companion column.

Dates, times, instants, and durations

Temporal modeling errors often survive for years. Decide whether the value is:

• a calendar date without a time, such as birth_date;
• a local civil time, such as a recurring store opening time;
• a local date-time tied to a named time zone, such as an appointment;
• a global instant, such as created_at;
• a duration with an explicit unit, such as latency_ms.

For global events, normalize storage to a consistent instant representation and preserve the originating time-zone context when the business needs it. Never infer a time zone from the server location.

Text length, Unicode, and collation

Choose text limits from domain rules, not from guesses. A display name, legal name, filename, URL, and free-form description have different needs. Ensure the encoding supports the languages and symbols the application accepts.

• A database length constraint is an integrity rule, not a user-interface design.
• Character count and byte count may differ for Unicode text.
• Case-insensitive uniqueness depends on collation or normalized comparison, not only lowercasing in one screen.
• Do not store large documents in dozens of numbered columns; use a coherent text/document model.

Statuses, categories, and flags

A Boolean is correct only when the domain has two stable states. is_active may be appropriate; order_complete may be too weak if orders can be pending, paid, packed, shipped, cancelled, or refunded.

A repeatable selection checklist

1. Write one sentence defining the value.
2. List valid, invalid, minimum, maximum, and missing cases.
3. State whether exactness is required.
4. Specify unit, time zone, currency, encoding, or format where relevant.
5. List required operations: arithmetic, ordering, searching, joins, grouping, or date arithmetic.
6. Choose the narrowest clear type that safely covers future valid values.
7. Add NOT NULL, CHECK, UNIQUE, or foreign-key constraints.
8. Test boundary values and invalid inputs in every supported DBMS.

Lab: refactor a weak table

The following design accepts almost any text and therefore preserves little meaning:

CREATE TABLE weak_order (    id TEXT,    customer_email TEXT,    ordered_at TEXT,    total TEXT,    status TEXT,    gift TEXT);

Refactor it into a constrained SQLite design. The checks are intentionally understandable rather than a complete email or timestamp standard.

DROP TABLE IF EXISTS purchase_order;CREATE TABLE purchase_order (    order_id INTEGER PRIMARY KEY,    external_reference TEXT UNIQUE,    customer_email TEXT NOT NULL        CHECK (instr(customer_email, '@') > 1),    ordered_at TEXT NOT NULL        CHECK (datetime(ordered_at) IS NOT NULL),    currency_code TEXT NOT NULL        CHECK (length(currency_code) = 3),    total_minor INTEGER NOT NULL        CHECK (total_minor >= 0),    status TEXT NOT NULL        CHECK (status IN ('pending', 'paid', 'cancelled')),    is_gift INTEGER NOT NULL DEFAULT 0        CHECK (is_gift IN (0, 1))) STRICT;INSERT INTO purchase_order    (external_reference, customer_email, ordered_at,     currency_code, total_minor, status, is_gift)VALUES    ('WEB-000127', 'learner@example.com',     '2026-08-05T11:30:00Z', 'EUR', 2499, 'paid', 1);

The improved table distinguishes internal identity from an external reference, preserves leading zeros, validates missingness, documents currency, stores exact minor units, constrains the status set, and enforces the Boolean-style flag.

Common mistakes

Using a smaller integer only to save bytes

A range failure is more expensive than a few bytes. Select a range with realistic growth and vendor behavior in mind.

Encoding multiple facts in one string

Values such as "EUR:12.99:PAID" are difficult to validate, search, join, and evolve. Store independent facts in independent columns.

Using sentinel values instead of NULL

Dates such as 1900-01-01 and numbers such as -1 blur “missing” with legitimate values. Model absence deliberately.

Making every column nullable “for flexibility”

Nullability should reflect the domain and lifecycle. Required facts should be NOT NULL; genuinely optional or not-yet-known facts may be nullable.

Checkpoint and practice

Summary and next lesson

Type selection is domain modeling. Preserve identity, exactness, units, temporal meaning, valid states, and missingness before optimizing storage. The next lesson focuses on missing information itself and the three-valued logic SQL uses when predicates involve NULL.

References

• SQLite: STRICT Tables
• PostgreSQL: Numeric Types
• PostgreSQL: Date/Time Types
• SQL Server: Data Types