Glossary

Database table

A table is one database object, not the entire database or a PHP object. A well-designed table describes one kind of record and its relationships to other data.

Short definition

Rows hold records; columns give their data meaning.

A table has predefined columns, such as the order ID, status, currency, and creation date. One row represents a specific order. The data type, default value, and constraints determine what a column can contain; a primary key gives the record a stable identity.

A table does not define the order of its rows. SQL without ORDER BY does not guarantee insertion order in its output. Nor is a table a spreadsheet: its design accounts for keys, relationships, concurrency, and data being read by different parts of the application.

The problem it solves

Storing one type of record in a readable structure

Tables separate an order as a whole from its repeating line items, its customer, or a product.

orders for order headers
order_items for any number of order items
customers for customer details
products for the catalogue and current product attributes
inventory_movements for individual inventory movements

SQL example

PostgreSQL tables for orders and items

The example uses PostgreSQL types. order_items is a separate table because an order has zero or more items.

CREATE TABLE orders (
    id uuid PRIMARY KEY,
    customer_id uuid NOT NULL,
    status text NOT NULL,
    currency char(3) NOT NULL,
    total_amount numeric(12, 2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id uuid PRIMARY KEY,
    order_id uuid NOT NULL REFERENCES orders(id),
    product_name text NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(12, 2) NOT NULL
);

How it works

From order design to connected records

Text alternative: orders holds the order header; order_items holds its items and references orders.

Defining the record The design distinguishes an order, an order item, and a product as facts with their own life cycles.
Choosing columns Each column is assigned a meaning, type, nullability, and optional default value.
Identity and rules A primary key identifies the row; constraints prevent invalid values.
Inserting rows The application adds the order and its line items as separate records, often in a single transaction.
Relationships and queries Foreign keys and JOIN make it possible to combine data without storing a product list in one text field.

Important concepts

Structure and data play different roles.

Table names and rules should describe data clearly for future imports and reports as well.

Row and column

A row, or record, is one order. A column, or attribute, holds one value, such as currency or created_at.

Data type and NULL

The type restricts permitted values. NULL is a separate state for a missing or inapplicable value, not zero or an empty string.

Keys and constraints

A primary key uniquely identifies a row. A foreign key connects tables; UNIQUE, NOT NULL, and CHECK protect additional rules.

Default value

DEFAULT supplies a value when an INSERT omits it. It does not automatically mean that the chosen value is always right for the business process.

View

A view is a stored representation of a query. It is not the same as a regular table that can be written to without additional conditions.

Benefits and limitations

More tables do not make a design good by themselves.

Benefits

a readable structure for one kind of record
types and rules close to the stored data
querying and joining multiple related records
a foundation for integrity, transactions, and indexes

Common mistakes

storing multiple values as CSV in one text column
storing stable relationships in JSON just to get started quickly
unclear names that do not convey the meaning of the data
a table without suitable identity and relationships
relying on implicit row order without ORDER BY

Practical example

An order and its line items have different granularity.

The orders table holds one order: its ID, customer, status, currency, price, and time. Items belong in order_items because there can be several of them, they change individually, connect to products, and are aggregated. A product list stored as text would complicate these operations.

However, the number of tables should not grow mechanically. Their boundaries must reflect what constitutes a separate fact, how the data changes, and what the application asks of it.

What to keep in mind

The structure should support future work with the data.

A table design should be evaluated against reads, changes, integrations, and historical data.

define the meaning, type, and NULL or DEFAULT rules for every column
move repeating items into a table when they have their own identity or queries
add a stable primary key and foreign keys for owned internal relationships
always use ORDER BY when ordering results
do not use the number of tables as a measure of model quality

Common questions

Tables in practice

Is a row the same as a PHP object?

No. An object is an application representation, while a row is a database record. They can be mapped to each other, but the relationship is not necessarily one-to-one.

Can a table contain identical rows?

SQL allows this without a primary or unique constraint. Most business tables, however, need a stable identity.

Why not put order items in JSON?

JSON may suit flexible supplementary data, but items are usually filtered, aggregated, connected, and protected by relationships; a separate table expresses this better.

Does a table define the order of its rows?

No. Output order must be specified explicitly with ORDER BY.

How I apply this principle in practice

I design tables around real data facts.

In e-commerce solutions, I work with orders, products, inventory, and integrations so that changes and relationships remain clear and traceable.

How I work with databases Discuss your project without obligation