Business rules are the logic that makes the application work correctly. A 10% discount for orders above Rs 5,000. 18% GST on non-essential items. Account status changes from "active" to "suspended" after 3 failed payments. These rules live in the code, in stored procedures, or in triggers. Your job is to verify the database reflects them correctly.
-- Business Rule: 10% discount for orders above Rs 5000
-- Find orders that SHOULD have a discount but do not
SELECT order_id, subtotal, discount_amount
FROM orders
WHERE subtotal > 5000
AND (discount_amount IS NULL OR discount_amount = 0);
-- Find orders where discount percentage is wrong
SELECT order_id, subtotal, discount_amount,
ROUND(discount_amount * 100.0 / subtotal, 2) AS actual_pct
FROM orders
WHERE subtotal > 5000
AND ABS(discount_amount - subtotal * 0.10) > 0.01;
-- If actual_pct is not 10.00, the calculation is broken
-- Find orders that got discount but should not have
SELECT order_id, subtotal, discount_amount
FROM orders
WHERE subtotal <= 5000
AND discount_amount > 0;-- Business Rule: 18% GST on applicable items
SELECT oi.item_id, oi.unit_price, oi.tax_amount,
ROUND(oi.unit_price * 0.18, 2) AS expected_tax,
oi.tax_amount - ROUND(oi.unit_price * 0.18, 2) AS tax_diff
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.is_taxable = true
AND ABS(oi.tax_amount - ROUND(oi.unit_price * 0.18, 2)) > 0.01;
-- Find taxable items with zero or NULL tax
SELECT oi.item_id, p.name, oi.unit_price, oi.tax_amount
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.is_taxable = true
AND (oi.tax_amount IS NULL OR oi.tax_amount = 0);Status fields follow specific transition rules. An order goes from "pending" to "confirmed" to "shipped" to "delivered". It cannot jump from "pending" to "delivered." A loan goes from "applied" to "approved" to "disbursed" — never from "rejected" to "disbursed." These transitions are business-critical.
-- Find invalid status transitions using audit log
SELECT al.order_id, al.old_status, al.new_status, al.changed_at
FROM order_audit_log al
WHERE (al.old_status, al.new_status) NOT IN (
('pending', 'confirmed'),
('confirmed', 'shipped'),
('shipped', 'delivered'),
('pending', 'cancelled'),
('confirmed', 'cancelled')
);
-- Any rows here = invalid status transition = critical bug
-- Find orders in impossible states
SELECT order_id, status, payment_status
FROM orders
WHERE status = 'delivered' AND payment_status = 'unpaid';
-- Cannot deliver an order that is not paid
-- Find loans that jumped from rejected to disbursed
SELECT loan_id, status, previous_status, updated_at
FROM loans
WHERE status = 'disbursed' AND previous_status = 'rejected';Ask the product manager for a state transition diagram. If they do not have one, build it yourself from the requirements. Then write SQL to validate that no records violate the allowed transitions. This single practice catches more business logic bugs than anything else.
Q: How do you validate business rules at the database level?
A: I translate each business rule into a SQL query that finds violations. For discount rules, I check if eligible orders received the correct discount and ineligible orders received none. For tax calculations, I compare stored tax with the expected percentage. For status transitions, I query the audit log for invalid jumps (like pending to delivered). For financial rules, I verify conservation of money — the system total before and after a transaction should be the same. Each violation query should ideally return zero rows. Any result is a bug.
Key Point: Business rule validation translates each rule into a SQL query that finds violations — discounts, taxes, status transitions, and financial calculations.