Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
SQL feels clearer when you model the durable facts and how they relate before thinking about the nested objects a screen needs. A database can store customers, orders, and order items in separate tables; a query joins those facts, and application code can shape the result into an API response.
Why doesn’t my database look like my frontend data?
A frontend often works with a nested object because that shape is convenient for rendering. For example, an order-detail screen might consume an order with a customer object and an array of items. The database has a different job: it records facts and relationships in a form that can be constrained and queried.
Start by listing what the system must remember: a customer has identifying details; an order belongs to a customer; each order contains products; and each order item has a quantity. These are facts and relationships, not a required JSON shape. A query can combine the relevant rows, and backend code can map them into the structure the screen expects.
Recommended Free Tools
How do I model relationships in SQL?
Use a foreign key for a one-to-many relationship
A primary key identifies a row. A foreign key constrains a value in one table to match a referenced row in another, preserving referential integrity. For a customer with multiple orders, each order can carry a customer identifier that references the customer table. The customer row does not need to contain a stored array of all its orders.
#1 Best Overall
PostgreSQL’s documentation explains foreign keys and referential integrity in its table-constraint documentation. The core idea applies broadly to relational modeling, though SQL engines can differ in features and syntax.
Use a junction table for a many-to-many relationship
An order can contain multiple products, and a product can appear in multiple orders. A junction table—here, order_items—represents that many-to-many relationship with a foreign key to each side. It can also hold facts about the relationship itself, such as quantity.
| Relation | What it records | Relationship represented |
|---|---|---|
customers |
Customer facts, identified by a primary key | One customer can be referenced by many orders |
orders |
Order facts, including a customer foreign key | Each order belongs to a customer |
products |
Product facts, identified by a primary key | A product can be referenced by many order items |
order_items |
Order and product foreign keys, plus facts such as quantity | Connects orders and products; one row records an item in an order |
This structure keeps product facts separate from order-specific quantity. PostgreSQL’s foreign-key tutorial demonstrates foreign keys as a way to connect tables and maintain referential integrity.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteHow do I join related tables for an API response?
A JOIN pairs rows according to a condition. In an order-detail query, join the order to its customer and order items, then join each item to its product. The following PostgreSQL-oriented example returns one row per order item:
SELECT
o.id AS order_id,
c.id AS customer_id,
c.name AS customer_name,
oi.quantity,
p.id AS product_id,
p.name AS product_name
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 ON clauses make the matching relationships visible: an order’s customer identifier matches a customer key, and each order item’s product identifier matches a product key. PostgreSQL’s documentation on joins between tables notes that explicit join syntax makes the join condition easier for readers to distinguish from other query conditions.
Choose the join based on which rows should remain
| Join type | Which rows remain? | Useful distinction |
|---|---|---|
INNER JOIN (written as JOIN) |
Only rows with a match on both sides | An order without a matching row on the joined side is omitted |
LEFT JOIN |
Every row from the left side, including those without a right-side match | Columns from an unmatched right-side row are NULL |
For example, use a left join when the query should retain an order even if it has no matching row from the joined table. Use an inner join when only matches belong in the result. PostgreSQL’s join documentation describes these differences.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why does the query repeat order and customer data?
If an order has several items, the query returns several rows. Order-level and customer columns therefore repeat once for each item. That is a normal consequence of combining related rows into a flat result, not evidence that the database stored duplicate orders.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Application code can group the rows by order and build a nested response with one order, its customer, and an item array. The database model remains organized around related facts; the response model is organized around what the consumer needs. Not every backend has to assemble the result in precisely this way, but the separation makes it easier to tailor query results without requiring storage to mirror a component tree.
Where should a frontend engineer go next?
PostgreSQL’s official tutorial introduces creating tables, querying, joins, foreign keys, transactions, and related fundamentals. Its material is specific to PostgreSQL; treat dialect-specific syntax and behavior accordingly when working with another database.
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.
