Jan Mikoláš
Glossary
Relational database
The relational model is more than data displayed in tables. It rests on the meaning of columns, row identity, relationships, constraints, SQL queries, and transactions.
Short definition
Tables and keys describe related facts.
A relational database keeps one kind of fact in each table and expresses relationships through keys. A customer table does not have to repeat customer details in every order; the order references the customer instead. The database can then join related rows in a query, protect references with a foreign key, and apply changes in a transaction.
The relational model is neither Doctrine ORM nor a spreadsheet. An ORM is an application layer that maps PHP objects, while a spreadsheet does not typically enforce the same integrity or concurrency. PostgreSQL is an example of a relational database management system, while SQL is the language used to work with relational data.
The problem it solves
Relationships that must not end up as unstructured text
The relational model is most valuable where data is interconnected and rules must hold even under concurrent use.
- the order process, line items, payments, refunds, and inventory
- products, variants, prices, categories, and manufacturers
- users, organisations, roles, and tenant context
- imports linked to unique external identifiers
- internal systems with reporting across several kinds of data
Practical model
Relationships in an online store
An order contains customer_id. An order item contains order_id and product_id. The item’s price and name can be a historical snapshot of the purchase, while the products table holds the current catalogue state. This intentional duplication has a different temporal meaning and is not the same as uncontrolled copying of data.
When an order is created, the database validates foreign keys and stores the order and its line items in a transaction. A query with JOIN then assembles the required view for the administration interface.
How it works
A customer, order, and line item in one model
Text alternative: one customer has multiple orders; an order has multiple line items, and each item references a product.
- Customer The customers table holds the customer’s stable identity and details.
- Order The orders table contains customer_id, which references the customer and expresses a one-to-many relationship.
- Order item The order_items table contains order_id and product_id, placing repeated items in separate rows.
- Keys and constraints Primary keys establish identity, while foreign keys protect valid relationships between tables.
- SQL join A query joins the order, customer, and line items for an administration interface or API without copying the entire record.
Important concepts
A relation is more than a few tables next to one another.
These concepts describe what the data means and how it can be related.
Table, row, and column
A table stores one type of record. A row is a specific record, and a column is its named attribute with a data type.
Value domain
A data type and additional rules determine which values make sense in a column. A price, date, and email address are not the same kind of data.
Keys and relationships
A primary key identifies a row. A foreign key references another row and protects referential integrity.
One-to-one, one-to-many, and many-to-many
One order has multiple line items. A many-to-many relationship, such as products and categories, is represented by a junction table.
Normalization and transactions
Normalization limits uncontrolled repetition of facts. Transactions keep related local changes together.
Benefits and limitations
Integrity does not mean splitting data without restraint.
Benefits
- clear relationships and protection against invalid references
- SQL queries and aggregations across related data
- transactional changes and database constraints
- less duplication of facts in a transactional model
Common mistakes
- treating every table as an application object
- omitting constraints from relationships, allowing invalid references
- assuming that JSON automatically replaces the entire relational model
- splitting data into as many tables as possible regardless of its meaning
- confusing an ORM with the relational database itself
Practical example
An order is not one endless document.
The customers → orders → order_items → products model expresses that a customer can have multiple orders and a product can appear in many line items. A junction table applies the same principle to a many-to-many relationship, such as products ↔ categories.
A JSON column can naturally hold the changing payload of an external API. However, the order’s core data, prices, identities, and relationships often need tables, types, and constraints to remain easy to query and consistent.
What to keep in mind
A relationship must have meaning, an owner, and a rule.
A data model should reflect real facts and changes, not just the current form.
- identify which fact belongs in which table
- choose stable row identities and foreign keys for internal relationships
- measure queries before adding indexes or denormalization
- distinguish current catalogue data from a historical order snapshot
- let the ORM support working with data without hiding the underlying model
Common questions
The relational model in practice
Is every database with tables relational?
Not necessarily. Relations, keys, integrity, and the way data is handled matter more than a tabular appearance alone.
Do I always have to use foreign keys?
They are often a valuable safeguard for internal data where the database owns both ends of the relationship. At the boundary of an external service, the database usually cannot provide the same guarantee.
Does JSON replace the relational model?
No. It works well for flexible supplementary data, but key relationships often lose direct integrity, constraints, and convenient queries.
Is Doctrine ORM a relational database?
No. It is a PHP tool for working with relational data; the database, SQL, and their rules remain a separate layer.
How I apply this principle in practice
I design relational data around real relationships and changes.
For online stores and internal systems, I design models for orders, products, integration identities, and query performance around clearly separated facts.