What Is Relational Model? A Guide

The relational model separates the meaning of data from the machinery used to store and retrieve it. An application can ask which customers placed an order without specifying where their records sit on disk or which access path the database should follow. Tables, keys, constraints, and operations make that separation practical.

This guide follows a small order-management example through the model's components, query behavior, relationships, and normalization rules. It also explains where relational design helps, where implementation choices matter, and how the same tables can support graph queries when the questions center on connected entities.

What is a relational model?

The relational model is a logical data model that represents information as relations, commonly presented as tables. A relation contains tuples, represented as rows, with named attributes, represented as columns. Each attribute has a domain of permitted values. For example, an order date belongs to a date domain, while an order quantity might be restricted to positive integers.

Edgar F. Codd introduced the model in his 1970 paper, A Relational Model of Data for Large Shared Data Banks. A central objective was data independence: applications should not depend on the physical organization of stored records.

A relational database management system, or RDBMS, implements these ideas. SQL is a language used to define and query data in such systems. The model, the language, and the database software serve different roles.

In classical relational theory, a relation is an unordered set of tuples, so duplicate tuples have no place in it. SQL permits duplicates; uniqueness constraints can prevent them in stored data, and query operations such as DISTINCT can remove them from results. That distinction becomes important when counting rows or combining query results.

How does the relational model work?

Consider an order-management database with four tables:

customers(customer_id, customer_name, email)
orders(order_id, customer_id, order_date)
products(product_id, product_name)
order_items(order_id, product_id, quantity, unit_price)

Each customer has an identifier. Each order records the identifier of its customer. An order item connects an order to a product and records the quantity and price paid. For this example, each product appears at most once per order; repeated units increase the quantity.

Applications insert, update, and delete records through the DBMS. Queries combine the relevant rows according to conditions. To retrieve customer 42's orders:

SELECT c.customer_name, o.order_id, o.order_date
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.customer_id = 42
ORDER BY o.order_date, o.order_id;

The join matches customer identifiers, the filter restricts the result to one customer, and the selected columns define the output. A customer with three orders contributes three rows. This is the behavior of an inner join.

The query describes the required result. The DBMS chooses an execution plan. The explicit ORDER BY makes the output order predictable; insertion order provides no such guarantee.

Relational model architecture

The relational model defines logical structures and operations. It does not prescribe a particular server topology or storage engine. A useful way to understand an implementation is to separate its schema, query processing, and physical storage.

The logical schema defines tables, attributes, domains, keys, and constraints. The schema describes what a valid database can contain; the database instance is the data present at a particular moment. Adding an order changes the instance. Adding an attribute changes the schema.

The query processor turns a statement into executable work. PostgreSQL, for example, documents a query path through parsing, rewriting, planning, and execution. Its planner evaluates alternative access paths, and its executor performs the selected scans, joins, and other operations.

The storage and transaction subsystems manage the physical records and coordinated changes. Indexes help locate data, while transaction mechanisms govern how changes commit and become visible. These are implementation responsibilities, distinct from the logical definition of a relation.

This separation allows an administrator to add an index without changing what a customer or order means. A schema change, however, can affect application queries and requires coordination. Data independence reduces coupling to storage decisions; it does not eliminate the need to manage logical changes.

Types of keys in the relational model

Keys identify records and provide stable references between tables. Several terms describe overlapping properties, so a single key can belong to more than one category. The distinction between superkeys, candidate keys, and primary keys follows the relational model's definition of uniqueness and minimality.

Minimality means no attribute can be removed while retaining uniqueness. It does not mean that every candidate key must contain the same number of attributes.

Natural and surrogate keys describe where identifiers come from. A natural key has business meaning, such as a product code. A surrogate key is assigned for identification, such as a generated customer ID. Choosing a surrogate does not remove the need to enforce business uniqueness elsewhere.

In SQL, a primary key is unique and non-null. A foreign key can reference an eligible unique key as well as a primary key; it does not have to be unique in the referencing table. SQL Server's key documentation illustrates these distinctions.

Relational model constraints

Constraints specify which data states are valid. Three core categories cover values, identity, and references.

Domain constraints limit attribute values. A data type establishes a basic domain, while additional rules can require a positive quantity or a nonnegative price.

Key and entity integrity constraints prevent duplicate identifiers and missing primary-key values. Two customers cannot share the same primary key, even if their names happen to match.

Referential integrity constraints keep references valid. If an order names a customer, that customer must exist. Requiring the customer reference also needs a non-null constraint; a nullable foreign key can represent an absent relationship.

Assuming the parent tables have their stated primary keys, this SQL definition expresses the order-item rules:

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

The NOT NULL declarations matter. In PostgreSQL, a CHECK constraint accepts an expression that evaluates to unknown because of nulls. A positive-quantity check alone therefore does not require a quantity.

These rules reject an item with zero quantity or a nonexistent product. They cannot establish whether the customer intended to order it. Constraints enforce the rules encoded in the schema; application workflows still determine which valid changes should occur.

Relational algebra and operations

Relational algebra describes operations that take relations as inputs and produce a relation as output. That common result type lets operations compose: filter some orders, join them to customers, then retain selected attributes. Berkeley's relational algebra notes explain the operators behind this process.

Union, intersection, and difference require compatible schemas. Selection corresponds to SQL filtering with WHERE; projection corresponds to choosing columns, with an important duplicate-handling distinction.

For example, SELECT customer_id FROM orders can return the same customer repeatedly. SELECT DISTINCT removes those duplicates, matching classical projection. Choosing between these behaviors changes the answer: counting orders and counting customers are different questions.

Relationships in the relational model

Relationships are represented through values and constraints. Their cardinality describes how many records can participate on either side.

One-to-many. A customer can place multiple orders, while each order in our example belongs to one customer. The foreign key sits in orders. Requiring it ensures each order has a customer, but does not require every customer to place an order.

One-to-one. An optional customer profile can use customer_id as both its primary key and a foreign key. That permits at most one profile per customer. It still permits a customer without a profile.

Many-to-many. Orders contain multiple products, and products appear in multiple orders. The order_items junction table represents each association and holds its quantity and purchase price. Microsoft's database design guidance describes these relationship patterns.

The same structure supports longer questions, such as finding customers connected through shared purchases. SQL can express these through joins and, for repeated traversal patterns, recursive queries. As paths grow, query complexity and intermediate result sizes become workload considerations.

PuppyGraph lets teams define a semantic model of entities and relationships over those existing tables. A graph schema could map customers, orders, and products to nodes, with relationships expressing who placed an order and which products it contains. The mapping makes business relationships explicit for reuse across queries.

Its default direct-query path reads supported SQL databases, warehouses, and lakehouses without requiring graph-specific ETL or a persistent duplicate graph dataset. Analysts can query that model using openCypher or Gremlin while the underlying tables remain in their source systems. Relational storage and a graph view can therefore serve different questions over the same data.

Data normalization in relational models

Normalization organizes relations around dependencies to reduce redundant facts and the anomalies they cause. A functional dependency means one set of values determines another: a customer ID determines that customer's current email. Suppose one order-line table repeats the customer's current name and email alongside every purchased product. Changing an email requires updating many rows. Deleting the customer's last order could erase their contact details, and recording a customer without an order becomes awkward.

The first three normal forms provide a practical progression.

First normal form (1NF) requires atomic attribute values within the chosen domains. For this example, each order item has one product ID; products are not packed into a comma-separated list or a repeating series of product_1, product_2, and product_3 columns.

Second normal form (2NF) adds that every non-prime attribute depends on the whole of each candidate key, not a proper subset. A non-prime attribute belongs to no candidate key. These rules exclude partial dependencies. In an order-line relation keyed by (order_id, product_id), a product name depends only on product_id, so it belongs in products. Quantity depends on the pair.

Third normal form (3NF) addresses problematic transitive dependencies. In our example, an order identifies a customer, and the customer identifies their current email. Keeping that email in orders repeats a customer fact across orders. It belongs in customers, referenced through customer_id. The formal normalization rules account for all candidate keys, not just the selected primary key.

Normalization preserves the ability to reconstruct facts through joins. Splitting tables arbitrarily is insufficient: the decomposition should be lossless, so joining the pieces does not invent associations or lose information.

Business meaning determines the dependencies. An order item's unit_price records the price paid at purchase time. It can legitimately differ from the product's current catalog price. Replacing it with a lookup of today's price would change the historical fact, not improve normalization.

Normalized tables also mean queries may need more joins. Denormalized reporting structures can be useful when a measured workload justifies them, provided their refresh and consistency rules are explicit.

Advantages of the relational model

The relational model is useful when independently maintained facts must remain consistent and support several ways of asking questions.

Explicit integrity rules make assumptions executable. An inventory application can reject references to nonexistent products. An enrollment system can prohibit duplicate student-course registrations. These guarantees depend on declaring and enforcing the relevant constraints.

Flexible querying allows new questions over existing facts. The order tables can serve customer histories, product-demand analysis, and revenue reports without requiring a separate storage structure for each question. Query cost still depends on data volume, indexes, selectivity, and the execution plan.

Controlled redundancy reduces the number of places where a changing fact must be maintained. A customer email has one logical home, while order items retain their own historical purchase prices. This makes ownership of each fact easier to understand.

Transactional implementations support coordinated updates. An order and its items can commit together, avoiding a partially saved purchase. PostgreSQL's transaction documentation explains this all-or-nothing behavior. Transaction guarantees come from the DBMS and its configuration, rather than from arranging data into tables alone.

These properties fit order processing, inventory, billing, and other applications with identifiable entities and explicit business rules. The trade-offs remain concrete: schema changes need coordination, joins need tuning, and distributing transactions across machines adds implementation work. Choosing a relational design establishes a logical foundation; it does not settle every storage, scaling, or analytical requirement.

Conclusion

The relational model combines a logical structure with operations and integrity rules. Keys identify facts, constraints reject invalid states, joins reconnect related records, and normalization limits avoidable duplication. A useful design starts by deciding what each row means and which dependencies must remain true as data changes.

Those tables can also support a graph view when analysis centers on paths and connected entities. Try the forever-free PuppyGraph Developer Edition and book a demo with the team to see how openCypher and Gremlin queries traverse relationships across warehouse and lakehouse tables, with no graph-specific ETL, while preserving the relational data that applications already use.