Orphans, mismatches and reconciliation

The hardest problems hide between tables, where each table looks fine on its own.

Orphans: rows pointing at something that doesn't exist.

SELECT COUNT(*)
FROM payments p
WHERE NOT EXISTS (SELECT 1 FROM invoices i WHERE i.id = p.invoice_id)

A foreign key would have stopped these (Tier 4, module 2), but Brightwell's tables don't have one.

Mismatches: two tables that should agree, but don't. Each invoice belongs to an order, and both record the customer:

SELECT COUNT(*)
FROM invoices i
JOIN orders o ON o.id = i.order_id
WHERE i.customer_id <> o.customer_id

Reconciliation: totals that should match, worked out two ways. An invoice's net_amount should equal its order's lines plus shipping. Work out the order totals in a CTE, join them to the invoices, and count the differences bigger than a penny (ABS(a - b) > 0.01).

Reconciliation is how finance teams catch problems: if the invoices and the order lines disagree, one of them is wrong, and someone has to find out which.

Checking your access…

Tables in Brightwell's database

departments3 columns
Brightwell's internal departments.
Columns in departments
Column Type Holds
id INTEGER Unique department number (primary key).
name VARCHAR Department name.
cost_centre VARCHAR Finance cost-centre code, e.g. CC200.

employees11 columns

Everyone who works or has worked at Brightwell, with a manager hierarchy (manager_id).

Columns in employees
Column	Type	Holds
`id`	INTEGER	Unique employee number (primary key).
`first_name`	VARCHAR	First name.
`last_name`	VARCHAR	Last name.
`email`	VARCHAR	Work email address.
`job_title`	VARCHAR	Job title.
`department_id`	INTEGER	Department they work in.
`manager_id`	INTEGER	Their line manager. NULL for the Managing Director.
`region`	VARCHAR	Sales region covered. Only set for the sales team.
`hire_date`	DATE	Date they joined.
`leave_date`	DATE	Date they left. NULL if they still work here.
`salary`	DECIMAL(12,2)	Annual salary in £.

suppliers6 columns

Companies Brightwell buys its stock from.

Columns in suppliers
Column	Type	Holds
`id`	INTEGER	Unique supplier number (primary key).
`name`	VARCHAR	Supplier company name.
`country`	VARCHAR	Country.
`city`	VARCHAR	City.
`contact_email`	VARCHAR	Ordering contact email. NULL where unknown.
`payment_terms_days`	INTEGER	Days Brightwell has to pay the supplier's invoices.

products12 columns

The product catalogue. Prices are current list prices, excluding VAT.

Columns in products
Column	Type	Holds
`id`	INTEGER	Unique product number (primary key).
`sku`	VARCHAR	Stock code, e.g. BW-PAP-1004.
`name`	VARCHAR	Product name.
`category`	VARCHAR	Product category.
`subcategory`	VARCHAR	Product subcategory.
`supplier_id`	INTEGER	Main supplier. NULL for a few products (data gap).
`unit_cost`	DECIMAL(12,2)	What Brightwell pays per unit, £ ex VAT.
`list_price`	DECIMAL(12,2)	Current selling price per unit, £ ex VAT.
`stock_qty`	INTEGER	Units in stock now.
`reorder_level`	INTEGER	Reorder when stock falls to this level.
`is_discontinued`	BOOLEAN	No longer sold.
`launched_on`	DATE	Date the product was first sold.

customers13 columns

Business accounts (wholesale) and consumers (online shop). Contains duplicate customers and inconsistent text on purpose.

Columns in customers
Column	Type	Holds
`id`	INTEGER	Unique customer number (primary key).
`name`	VARCHAR	Company name (Business) or person's name (Consumer).
`customer_type`	VARCHAR	'Business' or 'Consumer'.
`contact_name`	VARCHAR	Main contact at a business. NULL for consumers.
`email`	VARCHAR	Email address.
`phone`	VARCHAR	Phone number, in whatever format it was typed.
`city`	VARCHAR	City or town.
`region`	VARCHAR	UK region.
`postcode`	VARCHAR	Postcode.
`signup_date`	DATE	Date the account was opened.
`account_manager_id`	INTEGER	Sales rep who looks after a business account. NULL for consumers and some small business accounts.
`credit_limit`	DECIMAL(12,2)	Credit limit in £ for business accounts. NULL for consumers.
`marketing_opt_in`	BOOLEAN	Agreed to receive marketing emails.

marketing_campaigns7 columns

Marketing campaigns, their dates and budgets.

Columns in marketing_campaigns
Column	Type	Holds
`id`	INTEGER	Unique campaign number (primary key).
`name`	VARCHAR	Campaign name.
`channel`	VARCHAR	Email, Paid Search, Social, Print or Trade Show.
`target_segment`	VARCHAR	'Business', 'Consumer' or 'All'.
`start_date`	DATE	First day.
`end_date`	DATE	Last day.
`budget`	DECIMAL(12,2)	Spend in £.

orders9 columns

Customer orders. One row per order; the lines are in order_items.

Columns in orders
Column	Type	Holds
`id`	INTEGER	Unique order number (primary key).
`customer_id`	INTEGER	Who placed the order.
`order_date`	DATE	Date ordered.
`channel`	VARCHAR	'Online', 'Phone' or 'Sales Rep'.
`status`	VARCHAR	'Completed', 'Pending', 'Cancelled' or 'Returned'.
`employee_id`	INTEGER	Employee who took the order. NULL for online orders.
`campaign_id`	INTEGER	Campaign the order is attributed to. NULL if none.
`shipped_date`	DATE	Date dispatched. NULL if cancelled or not shipped yet.
`shipping_cost`	DECIMAL(12,2)	Delivery charge in £ ex VAT.

order_items5 columns

Order lines. Revenue for a line is quantity × unit_price (the price actually charged, ex VAT).

Columns in order_items
Column	Type	Holds
`id`	INTEGER	Unique order line number (primary key).
`order_id`	INTEGER	The order this line belongs to.
`product_id`	INTEGER	Product sold.
`quantity`	INTEGER	Units ordered.
`unit_price`	DECIMAL(12,2)	Price charged per unit at the time, after any discount, £ ex VAT.

invoices9 columns

One invoice per shipped order. Amounts in £.

Columns in invoices
Column	Type	Holds
`id`	INTEGER	Unique invoice number (primary key).
`order_id`	INTEGER	Order invoiced.
`customer_id`	INTEGER	Customer billed.
`invoice_date`	DATE	Date issued.
`due_date`	DATE	Date payment is due.
`net_amount`	DECIMAL(12,2)	Goods plus shipping, ex VAT.
`vat_amount`	DECIMAL(12,2)	VAT at 20%.
`total_amount`	DECIMAL(12,2)	net_amount + vat_amount.
`status`	VARCHAR	'Paid', 'Part paid', 'Unpaid' or 'Credited' (order returned).

payments5 columns

Money received against invoices. Refunds are negative amounts.

Columns in payments
Column	Type	Holds
`id`	INTEGER	Unique payment number (primary key).
`invoice_id`	INTEGER	Invoice paid.
`payment_date`	DATE	Date received.
`amount`	DECIMAL(12,2)	Amount in £.
`method`	VARCHAR	'Card', 'BACS', 'Direct Debit' or 'Cheque'.

web_sessions12 columns

Visits to the online shop, tracked from 01/04/2025. Each row is one visit and how far it got.

Columns in web_sessions
Column	Type	Holds
`id`	INTEGER	Unique session number (primary key).
`session_start`	TIMESTAMP	When the visit started (UK time).
`customer_id`	INTEGER	Logged-in customer. NULL for anonymous visitors.
`source`	VARCHAR	Organic, Direct, Paid Search, Email, Social or Referral.
`campaign_id`	INTEGER	Campaign that brought the visitor. NULL if none.
`device`	VARCHAR	Desktop, Mobile or Tablet.
`landing_page`	VARCHAR	First page seen.
`pages_viewed`	INTEGER	Pages viewed in the visit.
`viewed_product`	BOOLEAN	Viewed at least one product page.
`added_to_basket`	BOOLEAN	Added something to the basket.
`reached_checkout`	BOOLEAN	Started checkout.
`order_id`	INTEGER	Order placed in this visit. NULL if none.

Loading…