Performing a Database Audit: Reviewing the Store Application

Previously in this course, we explored database documentation standards to keep our schema readable. Now, we shift our focus to a comprehensive schema review to ensure our store application is structurally sound and ready for real-world usage.

A database audit is the final gatekeeping step in the development cycle. It’s where you prove that your theoretical design—the one we’ve been building throughout this course—actually behaves as expected when subjected to the rigors of data insertion and retrieval.

Verifying Table Relationships

Before testing functionality, you must confirm that your relational architecture is intact. As we discussed when normalizing the store schema, our tables must communicate through valid keys.

To perform a structural verify, run a query against the information_schema to ensure all foreign keys are pointing to the correct primary keys. This prevents "orphan" records that could crash your application logic later.

SQL
-- Check foreign key integrity
SELECT
    kcu.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.key_column_usage AS kcu
JOIN information_schema.constraint_column_usage AS ccu
  ON kcu.constraint_name = ccu.constraint_name
WHERE kcu.table_schema = 'public';

If any relationship returns a mismatch, it’s a sign that your foreign key implementation might be inconsistent.

Validating Constraints and Business Logic

A schema is only as strong as its constraints. In this final project review, we need to ensure that our rules (like NOT NULL or CHECK constraints) are actually enforcing business requirements.

For example, if you have a products table, you should verify that a price cannot be negative. If you missed this, now is the time to add it. Use the following audit check to ensure your constraints are active:

SQL
-- Audit active constraints on the products table
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

If you find missing constraints, refer back to our guide on using CHECK constraints to patch these holes before moving forward.

Testing End-to-End Data Flow

The ultimate test is a "happy path" simulation. We need to insert a customer, create an order, add items to that order, and retrieve the full invoice. This confirms that our JOIN logic and transaction handling are fully operational.

The Audit Checklist:

1. Insert: Add a new customer record.
2. Order: Link an order to that customer ID.
3. Line Items: Add products to the order using the order ID.
4. Retrieve: Use an INNER JOIN to verify the total order cost.

SQL
-- End-to-end audit: Creating a full transaction
BEGIN;
INSERT INTO customers (name, email) VALUES ('Audit User', 'audit@example.com');
INSERT INTO orders (customer_id, order_date) VALUES (1, CURRENT_DATE);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 101, 2);
-- Verify the result
SELECT c.name, o.id, p.name, oi.quantity
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
ROLLBACK; -- Clean up the audit data

Hands-on Exercise

Perform an audit on your own store database:

1. Run the information_schema query provided above and verify that your orders table correctly references the customers table.
2. Attempt to INSERT a product with a negative price. If the database accepts it, use ALTER TABLE to add a CHECK constraint.
3. Document your findings in a text file—every professional database audit needs a report.

Common Pitfalls

• Assuming Defaults: Don't assume constraints exist just because you thought about them during design; always run the pg_constraint query to verify.
• Ignoring Data Types: During your audit, check if you used INT where NUMERIC (for money) was required. Mismatched types often lead to silent precision errors.
• Skipping the Cleanup: Always use ROLLBACK during your functional tests so you don't clutter your development database with "Audit User" records.

FAQ

Q: Does a database audit require taking the server offline? A: Generally, no. Most schema checks are read-only and can be performed on a live system. However, modifying constraints on large tables can lock them; perform those in a maintenance window.

Q: Is this the last step before production? A: This audit ensures the schema is correct, but you should also review final project audit and optimization strategies, such as checking for missing indexes, before going live.

Recap

We have validated our relational architecture, confirmed that our constraints act as a safety net, and verified the end-to-end flow of our data. A solid schema review is the hallmark of a professional developer, ensuring that your application is reliable before it ever hits a production environment.

Up next: We will begin learning advanced string manipulation functions to format our output for reports.