SQL for Frontend Engineers: Model the Relationship First

A product card is one object in React.

An order summary is another object. A customer has an array of addresses. The API returns a comfortable tree, so it is tempting to imagine the database storing the same tree somewhere below the backend.

A relational database does not think in component-shaped trees. It stores facts and relationships.

That difference is where SQL starts feeling either confusing or surprisingly clean. If you begin with screens, the database looks fragmented. If you begin with what must remain true, the pieces make sense.

Let us model the checkout from the first two articles. Not as a complete commerce platform. Just enough to understand the decisions a frontend engineer eventually runs into.

Start with facts, not tables

Before writing SQL, I would write down the facts the system needs to preserve:

• a customer can place many orders;
• an order belongs to one customer;
• an order contains one or more line items;
• each line item records a quantity and the price charged at purchase time;
• changing a product's current price must not rewrite an old order;
• an order total cannot be negative.

That list is already most of the model. Tables are how we encode it.

CREATE TABLE customers (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(id),
  status TEXT NOT NULL,
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  currency CHAR(3) 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_id UUID NOT NULL,
  product_name TEXT NOT NULL,
  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),
  quantity INTEGER NOT NULL CHECK (quantity > 0)
);

Nothing here is clever. That is a strength.

The primary key identifies one row. The foreign key makes an invalid relationship impossible: an order cannot point to a customer that does not exist. The checks keep negative prices and zero-quantity line items out of the data, no matter which application path attempts the write.

Validation in the API gives users good feedback. Constraints in the database protect the truth when every other layer makes a mistake.

Relationships are the model

The line between customers and orders is one-to-many. One customer can have many orders, while each order points back to one customer through customer_id.

Orders and products look many-to-many: an order contains many products, and a product appears in many orders. The order_items table sits between them and turns that relationship into data of its own.

That middle table is not plumbing. Quantity belongs to the relationship between this order and this product. So does the price charged at that moment.

This is the first place where copying data can be correct. product_name and unit_price_cents deliberately snapshot purchase-time facts. If the catalog renames a product or changes its price tomorrow, yesterday's receipt must not change.

Normalization does not mean "never duplicate a value." It means each fact has a clear owner. The current product price belongs to the product. The charged price belongs to the order item. Those are two different facts that happen to match at checkout time.

A JOIN rebuilds the view you need

The frontend wants an order details object. The database stores related rows. A JOIN reconnects them for this query:

SELECT
  orders.id,
  orders.status,
  orders.total_cents,
  orders.currency,
  customers.email,
  order_items.product_name,
  order_items.unit_price_cents,
  order_items.quantity
FROM orders
JOIN customers ON customers.id = orders.customer_id
JOIN order_items ON order_items.order_id = orders.id
WHERE orders.id = $1;

The result contains one row per line item, so order fields repeat. That is normal. The backend maps those rows into the nested response the frontend wants.

This is a useful separation: storage is organized around integrity and querying; API responses are organized around the consumer's task. They do not need to have the same shape.

Trying to make every table mirror one screen usually creates awkward data. Screens change frequently. The fact that an order belongs to a customer changes much less often.

Transactions protect multi-step changes

Creating an order is not one write. We insert the order, insert its line items, and reserve inventory. If the third step fails, the first two should not remain as a convincing-looking half-order.

A transaction groups the writes into one decision:

BEGIN;

INSERT INTO orders (...);
INSERT INTO order_items (...);
UPDATE inventory
SET available = available - $1
WHERE product_id = $2
  AND available >= $1;

COMMIT;

If any required step fails, the application rolls the transaction back. Other readers see either the state before checkout or the completed order, not the awkward middle.

Transactions are not a way to make an entire distributed system atomic. A payment provider and an email service do not participate in your PostgreSQL transaction. Keep the database transaction focused, commit durable state, then hand slower external work to explicit follow-up processes.

That boundary will matter when this series reaches queues and background jobs.

Concurrency is where the model gets honest

Suppose only one unit remains. Two users place an order at almost the same time.

If both requests first read available = 1 and then separately write available = 0, both may believe they bought it. The UI cannot prevent this. Disabling a button only affects one browser.

The conditional update above makes the database perform the check and write together. The backend then verifies that exactly one row changed. One order proceeds; the other receives an out-of-stock result.

This is why I think of the database as more than persistence. It is the final place where concurrent claims about shared state meet.

If two requests can disagree, the invariant belongs where both requests must pass.

Frontend optimistic updates still have value. They make the interface responsive. They simply do not get the final vote on shared inventory, balances, permissions, or uniqueness.

Indexes answer a specific question faster

An index is often explained as "something that makes queries fast." True, but incomplete. An index makes a particular access pattern faster, while adding storage and write cost.

This query will become common:

SELECT id, status, total_cents, currency, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

A matching index is:

CREATE INDEX orders_customer_created_at_idx
ON orders (customer_id, created_at DESC);

The column order reflects the query: first narrow rows to one customer, then read them in the requested time order.

Adding indexes to every column is not a strategy. Each index must be updated when data changes. The right question is not "which fields look important?" It is "which query is slow, how does it filter, and how does it sort?"

Use the database's query plan instead of guessing. In PostgreSQL, EXPLAIN ANALYZE shows what actually ran, how many rows were examined, and where time went. It turns performance from folklore into evidence.

The N+1 problem starts as innocent code

Imagine loading twenty orders, then loading items for each order in a loop:

const orders = await orderRepository.findByCustomer(customerId);

for (const order of orders) {
  order.items = await orderItemRepository.findByOrder(order.id);
}

One query loads the orders. Twenty more load their items. That is N+1: one initial query plus one query per result.

It can hide behind a clean ORM relationship and look harmless in development with three records. Under real latency and larger result sets, it becomes a page that gets slower as the product succeeds.

The fix may be a join, a batched WHERE order_id IN (...) query, or an ORM include that genuinely batches. The important part is knowing how many database round trips the convenient abstraction produces.

An ORM can save enormous amounts of repetitive work. It cannot make the cost model disappear.

NULL is a domain decision

Nullable columns deserve more thought than they usually get.

Does shipped_at = NULL mean the order has not shipped, shipping does not apply, or old data was never migrated? Those are different states hiding behind the same absence.

Sometimes null is exactly correct. A pending order genuinely has no shipment timestamp yet. But if absence carries several meanings, make the state explicit with a status or separate relation rather than forcing every query and component to guess.

This maps directly to TypeScript. A nullable database field becomes string | null somewhere in the application. If frontend code keeps asking "why can this be null?", the uncertainty may begin in the data model, not the component.

Learn enough SQL to see through the abstraction

Typed query builders and ORMs are useful. I would rather write most application queries through one than concatenate SQL strings by hand.

But the abstraction should make SQL easier to produce, not unnecessary to understand. You still need to see the relationships, transaction boundary, indexes, constraints, and number of round trips. Otherwise a pleasant method chain can hide an expensive or incorrect operation.

The frontend parallel is familiar. A component library saves us from rebuilding every button, but we still need to understand HTML and accessibility. The database deserves the same respect.

Once the model is clear, SQL stops looking like backend punctuation. It becomes a precise way to ask questions about facts you have deliberately structured.

The next problem is changing that structure while old application instances are still running. That is where a harmless-looking rename becomes a production migration.

If there is a database concept you have been treating as "backend only," tell me which one. The best topics in this guide are exactly the boundaries frontend engineers are expected to cross without anyone first drawing the map.