GitLab Well Architected
Cookbook navigation

Example: understand how orders connect to customers

You're taking over an order service, and you'd like to understand what happens to an order when someone deletes a customer. A schema gives Duo something concrete to reason from, including relationships that an application diagram might leave out.

Share the schema

Use the original prompt with [MODULE/FEATURE] replaced by order processing, and include this SQLite schema in the conversation. You can discuss it without creating a database.

-- SQLite example of customer, order, and order-line relationships.
-- In an empty database, this creates three tables and one lookup index.
-- Enable foreign-key enforcement on each connection that changes data.
PRAGMA foreign_keys = ON;

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    external_payment_id TEXT,
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0)
);

CREATE INDEX orders_customer_id ON orders(customer_id);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    line_no INTEGER NOT NULL,
    product_name TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_no)
);

Follow an order through the answer

Duo's explanation should show how deletion works at each relationship. The database rejects deleting a customer while their orders remain. Deleting an order, however, also removes its lines. Together, these rules preserve the customer relationship and allow order lines to be removed with their parent.

Relationship What the schema tells you
Customer โ†’ orders A customer can have several orders. Each order needs an existing customer. RESTRICT prevents deleting that customer while orders remain.
Order โ†’ order lines An order can have several lines. Deleting it also deletes those lines through CASCADE.
Order โ†’ external payment external_payment_id is just a text field here. The schema doesn't enforce a relationship to a payment record.

The index on orders.customer_id supports looking up a customer's orders. The composite primary key on order_items also supports lookups starting with order_id. To find out whether those queries are fast enough for your workload, you'd need to run and measure them.

Add the missing application context

Suppose Duo describes external_payment_id as a foreign key. You can bring the discussion back to what the schema shows:

The payment record lives in another service. Our application stores its ID here,
but there's no local payment table. How does that change your explanation of
which relationships the database enforces?

When planning cleanup or recovery, you'll also need to understand how the application handles the payment. Deleting order lines in this database won't cancel a payment in another service, so check what the application does outside the database before changing deletion behavior.

Back to the prompt