Jan Mikoláš
Glossary
Database column and data type
A price, currency, time, email address, and external identifier are not the same kind of data. Their data types and rules should preserve that distinction from the moment they are stored.
Short definition
A type defines both permitted values and their meaning.
A column is one named piece of data in a row, such as total_amount or created_at. Its data type restricts the set of values and gives them semantics: integer is a whole number, numeric is an exact decimal value, and timestamptz is a point in time. Exact names and ranges can differ between databases.
The type is complemented by rules defining whether the value may be omitted, its default, and any other restrictions. Depending on the model, NULL represents a missing, unknown, or inapplicable value. It is not zero or an empty string, and SQL handles it differently.
The problem it solves
Storing values correctly before the application starts using them
Columns and types give data basic integrity and make it possible to sort, compare, and calculate values.
- an exact order amount and currency
- an import status and external identifier
- creation and modification timestamps
- email addresses, phone numbers, and other text data
- UUID for identity and JSONB for a flexible integration payload
SQL example
PostgreSQL columns for importing an order
The example shows exact types for values that will be filtered, validated, or processed further.
CREATE TABLE order_imports (
id uuid PRIMARY KEY,
external_order_id text NOT NULL,
total_amount numeric(12, 2) NOT NULL,
currency char(3) NOT NULL,
status text NOT NULL,
customer_email text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);How it works
From the meaning of a value to a safe change
Text alternative: first define the meaning of the value, then its type and rules, and finally verify both the migration and the transfer to the application.
- Meaning Determine whether the value is an amount, code, date, identifier, or supplementary data.
- Type and range Choose a suitable type and, where appropriate, precision or length based on the data’s meaning.
- NULL and DEFAULT Decide whether the value may be missing and what happens when an INSERT omits it.
- Additional rule If the type is not enough, add a constraint, such as a non-negative price or a unique external code.
- Migration and application Verify a type change against existing data and its mapping to PHP and the API.
Important concepts
Every type involves different trade-offs.
The type names in these examples come from PostgreSQL; other databases may use different types or behaviour.
Numbers
integer is used for whole numbers. numeric or decimal with specified precision and scale is suitable for monetary amounts; common floating-point types cannot represent many decimal values exactly.
Text and codes
text holds textual content. Text can also be the right type for a phone number or code because it may contain leading zeros, a plus sign, or spaces.
Date and time
date represents a calendar day, not a point in time. PostgreSQL timestamptz represents an instant and handles time-zone conversion.
UUID, JSON, and enum
A UUID is an identifier, not authorization. JSONB in PostgreSQL is suitable for flexible data. An enum can restrict stable values, but process changes then require a migration.
NULL, NOT NULL, and DEFAULT
NOT NULL is a constraint. DEFAULT applies when the value is omitted; it is not the same as making a value mandatory or replacing an application decision.
Benefits and limitations
A generic text column is not a universal solution.
Benefits
- rejecting nonsensical values at write time
- clearer calculations, comparisons, and ordering
- a foundation for constraints, indexes, and migrations
- a clearer contract between the database, PHP, and the API
Common mistakes
- storing monetary values as floating point
- confusing NULL with zero or an empty string
- using a date without a time zone as a point in time
- storing a phone number as an ordinary number
- storing all data as text or JSON without a reason
- assuming a UUID protects access to data by itself
Practical example
Types for importing an order
The price is numeric(12, 2), currency is char(3), status is text or a carefully designed enum, creation time is timestamptz, and email is text. An external marketplace ID is text: its format may contain zeros or other characters, while uniqueness is expressed by a separate constraint.
A flexible integration payload may belong in JSONB. That should not hide the order’s core data, such as its price, currency, identity, and relationships; these need specific types and rules.
What to keep in mind
Choose a type based on the meaning of the data, not just the current form.
Changing the type of a large table can be operationally demanding, so the model and migration should be reviewed early.
- use an exact decimal type or smaller units for amounts, depending on the domain
- consider the difference between a calendar date and a point in time
- do not treat a varchar length as complete business validation
- use NULL only for a clearly described state
- when using an enum, consider how often the business process changes
Common questions
Value types in practice
Is NULL an empty string?
No. It is a distinct state representing a missing or inapplicable value. Zero and an empty string are specific stored values.
Is a UUID a security measure?
No. It is an identifier type. Server-side authorization must always verify access to a specific record.
Why not store a price as a float?
Binary floating point cannot represent many decimal values exactly. An exact numeric type or a model using smaller units is more common for monetary amounts.
Is storing a date as text enough?
Only in exceptional cases. A data type allows the database to validate, compare, and work with the value more correctly.
How I apply this principle in practice
I choose types and rules based on the meaning of the data.
In database design, I account for exact values, historical data, integration payloads, and schema changes so the data remains readable over the long term.