Practice & Assessment
Test your understanding of Advanced Schema Patterns
Multiple Choice Questions
6You need to attach comments to both blog posts and product reviews. Referential integrity is critical. Which approach should you choose?
What is the main downside of soft deletes compared to hard deletes?
An order_items junction table stores unit_price as a snapshot column. Why is this better than referencing product price directly?
Which query correctly retrieves all active (non-deleted) customers with soft deletes implemented as a deleted_at TIMESTAMP column?
What advantage does a closure table have over an adjacency list for hierarchical data?
You want to record the full history of every update to an orders table, including before and after values. Which approach achieves this?
Coding Challenges
1Add Audit Trail to an Existing Orders Schema
Given an existing orders table (order_id, customer_id, status, total, created_at), write: (1) a CREATE TABLE statement for an orders_audit table capturing action type (INSERT/UPDATE/DELETE), the changed_by user ID, changed_at timestamp, and a JSON snapshot of the row; (2) an UPDATE trigger that inserts an audit row whenever an order's status changes; (3) a query that retrieves all audit rows for order_id = 5 ordered by changed_at descending. Use MySQL or PostgreSQL syntax. Time estimate: 25 minutes.
Mini Project
Social Platform Schema with Advanced Patterns
Design the full schema for a social posting platform supporting: users with soft deletes, posts and comments with a polymorphic association pattern (choose either approach and justify it), a self-referencing user_follows table (many-to-many on the same entity), post_likes as an enriched junction table with a liked_at timestamp, and a full audit table for posts. Write all CREATE TABLE DDL, add appropriate indexes, and include a 10-line design decision document as SQL comments explaining your polymorphic and soft-delete choices.