@jepemo
EXIT

Relational Data Modeling for CRUD Systems

Learning / Architecture And Platforms / Backend Development Foundations / 006

LESSON

Backend Development Foundations

006 25 min beginner

Relational Data Modeling for CRUD Systems

By the end of this lesson, you will be able to...

  • Turn a small backend workflow into tables, primary keys, foreign keys, and constraints.

  • Distinguish current facts, historical snapshots, and accidental duplicated data.

  • Review a CRUD data model for unclear ownership, missing relationships, and unsafe deletes.

Idea in one sentence: A relational model gives each durable fact one clear home, then connects facts with keys so CRUD handlers can change state without quietly corrupting it.

Core Insight

The orders API now has a runtime, dependencies, and a small request path. The next question is where the backend should keep durable state.

The first version is tempting:

POST /orders creates one big JSON object.
GET /orders/1001 returns the same object.
PATCH /orders/1001 changes fields inside it.

That feels simple because one request maps to one stored blob.

Then normal product behavior arrives:

A customer changes their email.
A product price changes for future orders.
An old invoice must still show the price paid.
Support needs all open orders for one customer.
A product is no longer sold, but old orders still mention it.

Now the big object is less clear. Is the email inside the order the current customer email, or the email at purchase time? If the product name changes, should old orders change too? If a product is deleted, what happens to order history?

The naive idea is:

Store whatever shape the API returns.

That works while the product has one screen and one operation. It breaks when different facts change for different reasons.

Relational data modeling asks a steadier question:

Which facts exist?
Who owns each fact?
How do facts point to each other?
Which invalid states should the database refuse?

This is a design lesson, not a SQL syntax lesson. The next lesson will focus on query shape, indexes, and transactions. Here we design the shape of durable truth.

The Small Situation

Use one recurring workflow:

Customers buy products.
Each order has one customer.
Each order has one or more line items.
Each line item names a product and a quantity.
Old orders must remain explainable after product data changes.

The backend needs common CRUD operations:

CRUD means create, read, update, and delete. CRUD is not the model. CRUD is pressure on the model.

If the model is clear, each handler knows what it may change. If the model is blurry, each handler invents its own version of truth.

The visible pieces are:

Plain meaning:

Normalization means not storing the same fact in five places unless you have a specific reason.

In this scenario:

The current customer email should live in one customer row. The purchase-time price should live with the order item because it is part of order history.

Technical name:

This separation of facts by identity and meaning is relational normalization.

The Naive Design

Start with one large table:

orders
  order_id
  customer_name
  customer_email
  shipping_street
  shipping_city
  product_1_name
  product_1_price_cents
  product_1_quantity
  product_2_name
  product_2_price_cents
  product_2_quantity
  order_status

This design is attractive because it matches one response:

GET /orders/1001

It is also easy to insert at the beginning. One handler writes one row.

The trouble appears when the workflow changes even a little.

If an order has three products, the table needs new columns or a strange workaround. If a customer changes email, the backend must decide whether to update old order rows. If product price changes, old charged prices may change by accident. If support needs all orders for one customer, the query relies on copied customer fields instead of customer identity.

The deeper problem is not that the table is ugly. The deeper problem is that ownership is unclear.

Who owns customer_email?
Who owns the product price?
Who owns order_status?
Is product_1_name current catalog data or historical invoice data?

When a model cannot answer ownership questions, CRUD code becomes guesswork.

A Better Boundary Between Facts

A relational first pass separates facts by what they are and how they change:

customers
  customer_id
  email
  display_name
  created_at

addresses
  address_id
  customer_id -> customers.customer_id
  line_1
  city
  country
  postal_code

products
  product_id
  sku
  name
  current_price_cents
  active

orders
  order_id
  customer_id -> customers.customer_id
  shipping_address_id -> addresses.address_id
  status
  created_at

order_items
  order_item_id
  order_id -> orders.order_id
  product_id -> products.product_id
  quantity
  unit_price_cents_at_purchase

This is not the only possible model. It is a useful first model because every table has a job.

customers owns current customer account facts.

products owns current catalog facts.

orders owns the lifecycle of one purchase.

order_items owns the relationship between one order and one product.

The order_items table is the important beginner step. An order can include many products. A product can appear in many orders. The relationship itself has facts: quantity and price paid at purchase time.

When a relationship has its own facts, it usually deserves its own table.

Check: Where should quantity live: on products, on orders, or on order_items?

Think first, then reveal.

Answer: order_items. Quantity is not a fact about the product in general, and it is not a fact about the whole order. It is a fact about one product inside one order.

A Worked Create Path

Trace one request through the model:

POST /orders
customer_id: 42
shipping_address_id: 12
items:
  - product_id: 7, quantity: 2
  - product_id: 9, quantity: 1

The handler should not simply trust that these IDs form a valid order. The model tells it what to verify and write.

Input:
  customer 42 wants products 7 and 9 shipped to address 12

Step 1: verify customer identity
  customers.customer_id = 42 exists

Step 2: verify address ownership
  addresses.address_id = 12 exists
  addresses.customer_id = 42

Intermediate state:
  the address is not only valid; it belongs to this customer

Step 3: verify product availability
  products 7 and 9 exist
  products.active = true

Step 4: create the order
  insert one row into orders
  status = 'pending'
  customer_id = 42
  shipping_address_id = 12

Step 5: create line items
  insert one row per requested product into order_items
  copy current_price_cents into unit_price_cents_at_purchase

Output:
  order 1001 exists with two line items and stable purchase-time prices

Now compare the naive failure.

If the backend only stored a big order blob, a future product price update might overwrite what the customer paid. Or the old order might keep a copied price, but the model would not say whether that copy is intentional.

The relational model makes the decision visible:

products.current_price_cents = current catalog fact
order_items.unit_price_cents_at_purchase = historical purchase fact

The duplicate-looking price is not accidental duplication. It is a snapshot with a reason.

So far, the model has done two useful things. It gave current facts a home, and it named the historical facts that should not change when current facts change.

Relationships and Cardinality

Relationships describe how rows connect. Cardinality means the "how many" shape of a relationship.

In the orders workflow:

These sentences are design decisions. They become foreign keys and table boundaries.

If one customer can have many orders, orders carries customer_id.

If one order can have many products, orders cannot have a single product_id.

If one order/product pair has quantity and purchase price, the pair needs a row in order_items.

This is where a relational model becomes more than a diagram. It turns product language into enforceable structure.

Constraints Make the Design Real

A diagram can say that an order item belongs to an order. The database can enforce it with a foreign key.

Useful constraints include:

For this model, constraints might say:

customers.email is UNIQUE and NOT NULL
orders.customer_id references customers.customer_id
order_items.order_id references orders.order_id
order_items.product_id references products.product_id
order_items.quantity must be greater than 0
orders.status must be one of pending, paid, shipped, canceled

Application validation still matters. It gives clients good error messages and protects the user experience.

Database constraints matter for a different reason. They protect durable state across every code path: public API, admin tool, migration script, background job, and future handler written by someone who has not read today's code.

Plain meaning:

Integrity means the stored data still obeys the rules that make it meaningful.

In this scenario:

An order item with quantity = -3 is not a valid order item, even if one buggy route tries to write it.

Technical name:

This is data integrity. Referential integrity means references point to rows that exist. Domain integrity means values fit their allowed meaning.

CRUD Through the Model

Run CRUD again, now with ownership visible.

Create:

POST /orders creates one orders row and several order_items rows. It points to existing customer, address, and product rows. It snapshots purchase-time prices.

Read:

GET /orders/1001 reads the order, its items, and product display details. If the response needs the charged price, it reads order_items.unit_price_cents_at_purchase, not products.current_price_cents.

Update:

PATCH /customers/42 changes customers.email. Current customer views should show the new email. Old order prices do not change because they are historical facts. If invoices need the original contact email, add an explicit invoice contact snapshot.

Delete:

Deleting is often not physical removal. If a product has appeared in old orders, removing its row may break history. A safer model often uses active = false, removed_at, or a status transition. The product stops appearing in new checkout flows, but old orders remain explainable.

The route name says what operation the user requested. The model says what facts the operation is allowed to change.

Check: A product is no longer sold. Should the backend physically delete the product row immediately?

Think first, then reveal.

Answer: Usually no. Old order items may still need to point to the product for history, support, and reporting. A status such as active = false is often safer than physical deletion.

Trade-offs and Limits

Normalization helps when facts change at different speeds. Customer profile data, catalog data, order status, and purchase history do not all have the same lifecycle.

The trade-off is clear: normalization buys safer writes and clearer ownership, but it can make reads and reporting more expensive. Reads may require joins. Developers need consistent names. Some API responses need assembly from several tables. Reporting may need a separate read model, cache, or warehouse table.

That does not make normalization bad. It means the design should be explicit.

The risky move is accidental denormalization:

We copied product_name into orders because it was convenient.
Nobody knows whether it is a snapshot, cache, or mistake.

Each copied value needs a rule:

This model does not solve every database problem. It does not choose indexes. It does not make complex queries fast by itself. It does not remove the need for transactions. It gives the next layer a clean structure to work with.

You can see the boundary when reads become slow because they assemble many relationships, or when reporting needs data shaped differently from the write model. That is a signal to design a read path deliberately, not to erase ownership from the write model.

Common Confusions

Confusion: The API Response Should Match One Table

Why it is tempting:

One endpoint returns one JSON object, so one table feels natural.

Better model:

An API response is a view assembled for a client. Storage should be organized around durable facts and ownership.

Confusion: Normalization Means No Copied Values Ever

Why it is tempting:

Beginners often hear "avoid duplication" as an absolute rule.

Better model:

Avoid accidental duplication. Intentional snapshots are valid when history must stay stable.

Confusion: Application Validation Is Enough

Why it is tempting:

The public API handler already checks input, so constraints may feel redundant.

Better model:

Application validation protects one entry point. Database constraints protect durable state across all entry points.

Practice

Review this feature:

Customers can save many payment methods.
An order uses exactly one payment method at checkout.
Support must see which payment method was used.
The system must never store a full card number.
Customers can remove a payment method from future checkout.

Design a small relational model.

A good answer should mention:

Model answer:

payment_methods belongs to customers. An orders row can reference the payment method used at checkout and may also store safe display data such as brand and last four digits. The full card number should not be stored in this backend model. Removing a payment method should usually prevent future use without breaking old order history, so a status or removed_at timestamp is safer than deleting the row immediately. Useful constraints include foreign keys from payment methods to customers and orders to customers, plus NOT NULL fields for the safe display data the support workflow requires.

Connections

The previous lesson treated dependencies as code that crosses your backend boundary. This lesson treats database rows as facts that cross time. In both cases, the backend needs clear ownership.

The next lesson uses this model to ask a different question: given these tables, what work does a SQL query cause, and when do indexes or transactions change the outcome?

Resources

Key Takeaways

PREVIOUS Package Managers, Dependency Boundaries, and Semantic Versioning NEXT SQL Query Shape, Indexes, and Transactions

← Back to Backend Development Foundations

← Back to Architecture And Platforms

← Back to Learning Hub