SQL gets easier when you stop trying to make database tables look like frontend objects. Model the durable facts first—customers, orders, products, and the links between them—then use a query and application code to shape those facts into the nested response a screen needs.
Why doesn’t your database look like your frontend data?
A frontend usually consumes objects shaped for a component or API: an order with a customer object and an array of items, for example. A relational database has a different job. It stores related facts in tables, and constraints help ensure those facts remain consistent. A query can combine the tables when an application needs a particular view.
As an Amazon Associate I earn from qualifying purchases.
Think of the database as the durable source of facts, the SQL query as a way to retrieve a useful view, and application code as one place to shape that view for a consumer. The response object does not have to mirror the storage model.
Recommended Free Tools
How do you model relationships in SQL?
Start with the facts checkout must preserve, rather than with the JSON you want to return:
#1 Best Overall
- A customer exists independently of any one order.
- An order belongs to a customer.
- An order can contain multiple products.
- Each product can appear in multiple orders.
- The quantity belongs to a particular product within a particular order.
One-to-many: put a foreign key on the many side
If one customer can place many orders, each order can carry a customer_id that refers to the customer row. A primary key identifies a row, such as customers.id or orders.id. A foreign key constrains a value to match a row in the referenced table, helping preserve referential integrity. PostgreSQL documents primary and foreign keys, including this relationship pattern, in its constraints documentation.
Many-to-many: make the relationship a table
Orders and products are typically many-to-many: an order can contain many products, and a product can appear in many orders. An order_items table represents that relationship with an order_id and a product_id, each referencing its corresponding row. It can also hold facts about the relationship, such as quantity. This is not merely a workaround for a nested array; the item and its quantity are facts about a product in a specific order.
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
created_at TIMESTAMP NOT NULL
);
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id)
);This PostgreSQL-oriented example uses a composite primary key to prevent the same product from appearing twice as separate rows for one order. If the application instead needs separate lines for the same product—for example, to preserve different options or prices—those facts would need to be represented in the key and columns accordingly. The important modeling question is what distinguishes one durable line item from another.
How do you join related tables for an API response?
A JOIN pairs rows according to a condition. In a detail view, explicit JOIN ... ON syntax makes the relationship being followed visible:
SELECT
o.id AS order_id,
o.created_at,
c.id AS customer_id,
c.name AS customer_name,
oi.product_id,
p.name AS product_name,
oi.quantity
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
WHERE o.id = 42;The matching clauses connect each order to its customer, each item to its order, and each item to its product. PostgreSQL’s documentation on joins explains that explicit join syntax separates the join condition from other filters, making the query’s meaning easier to read.
What the query returns
For an order with several items, this query returns one row per item. The order ID, date, and customer columns therefore repeat across those rows. That repetition is expected: a relational query returns rows, not automatically a nested object graph.
Application code can group the rows by order and build a response such as an order object with a customer object and an items array. Alternatively, a database query can aggregate values into a nested format when that suits the application and database. Neither approach changes the underlying facts or requires the stored tables to copy the frontend’s object shape.
When should you use INNER JOIN or LEFT JOIN?
| Join | Which rows remain | What happens to unmatched rows |
|---|---|---|
INNER JOIN |
Only rows with a match on both sides | Rows without a match are excluded |
LEFT JOIN |
Every row from the left side, plus matching rows from the right | Left-side rows without a match remain; right-side columns are NULL |
Use an inner join when a matching row is required for the result. Use a left join when you need to keep the left-side record even if the related row is absent—for example, to list all customers whether or not they have orders. In the checkout detail query above, inner joins are reasonable if a valid order must have a customer and at least one item. If the view must also show an order with no items, use a left join for the item relationship and account for null item columns in the result.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How to move from a screen shape to a relational model
- List durable facts. Identify the real entities and facts the system must preserve, such as customers, orders, products, quantities, and timestamps.
- Describe the relationships. Ask whether each relationship is one-to-one, one-to-many, or many-to-many. Place a foreign key on the many side for a one-to-many relationship; represent a many-to-many relationship with a table that references both sides.
- Choose keys and constraints. Give each row an identifier and use foreign keys for required references. Decide what makes a relationship row unique, and which values must be present.
- Write the query for the view. Join the needed rows using explicit
ONconditions, then select the columns the consumer needs. - Shape the response separately. Group repeated parent values into nested objects or arrays in application code, or use database-side shaping when appropriate.
Keeping those steps distinct avoids a common source of confusion: the database schema describes what is true and how records relate; the API response describes what a particular consumer needs to receive.
Where to learn the core SQL concepts
PostgreSQL’s official tutorial introduces tables, queries, joins, foreign keys, and transactions. Its examples and syntax target PostgreSQL; other SQL engines share many relational concepts but can differ in syntax and behavior, so consult the documentation for the database you use.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problems
