Data Modeling and Schema Design Questions

Direct answer

ON DELETE/ON UPDATE actions decide what happens to a dependent row when the row it references is deleted or its key changes: CASCADE propagates the change, SET NULL clears the reference, and RESTRICT/NO ACTION block the change entirely while any dependent rows exist; the right choice depends on whether the dependent row's existence is meaningful without its parent.

Structured elaboration

• CASCADE: deleting a users row also deletes all of that user's orders. Appropriate when the dependent row has no independent meaning without its parent (a user's shopping-cart items, say), but dangerous when the dependent rows themselves have standalone business value (deleting a user should probably not silently delete their entire order history).
• SET NULL: deleting a users row sets orders.referred_by_user_id to NULL instead of deleting the order. Appropriate when the reference is informational, not load-bearing (knowing who referred a customer is nice to have, but an order remains a valid, meaningful record even if the referrer's account is later deleted).
• RESTRICT/NO ACTION: block the delete entirely while any referencing row exists, forcing an explicit decision (reassign or manually remove the dependents first). Appropriate as the default for anything financially or legally significant, where a cascading or silently-nulled deletion could quietly destroy or corrupt a record that must be preserved.

Worked example

For users and orders: orders.user_id should almost certainly be RESTRICT or NO ACTION, not CASCADE, because deleting a user account should never silently delete their entire purchase and payment history; the correct operational flow is to first decide what happens to their orders (anonymize, reassign to a "deleted user" placeholder, or archive them) as an explicit step, not as an automatic side effect of the account deletion. By contrast, cart_items.cart_id referencing a carts row is a reasonable CASCADE: an abandoned cart's line items have no independent meaning once the cart itself is gone.

Trade-offs and pitfalls

• The main operational risk of CASCADE is exactly the "accidental mass-delete" scenario: deleting one row at the top of a deep reference chain can silently delete thousands of rows across many tables with no confirmation step, which is especially dangerous when the cascade chain is several levels deep and not all of it is obvious to whoever issued the original delete.
• Preventing accidental mass-deletes: default to RESTRICT for anything where deletion should require an explicit, reviewed decision, reserve CASCADE for genuinely dependent, no-independent-value child rows, and consider soft-deletes (an is_active/deleted_at flag, with no ON DELETE action ever firing because rows are never physically deleted) for anything where the safest default is "never let this disappear automatically at all."
• SET NULL requires the foreign-key column to be nullable, which is easy to overlook when initially defining the column as NOT NULL for data-quality reasons; if SET NULL is the intended behavior, the column's nullability constraint has to be designed for it from the start, not bolted on later.

Direct answer

The three realistic options for row-level change history are a separate audit table (one row per change, referencing the original table), database-level change-data-capture (CDC, reading the write-ahead log independently of the application), or an append-only pattern (the "table" itself is a log of versions, with the current state being the latest version per key); the right choice trades off write-path coupling against query convenience.

Structured elaboration

• Separate audit table (orders_audit(audit_id, order_id, changed_by, changed_at, change_reason, old_values, new_values)), populated either by a trigger on orders or by explicit application-level writes alongside the main update. Simple to query directly ("show me the history of this order"), but a trigger-based implementation couples every write's latency to the trigger's cost, and an application-level implementation risks missing history if some code path updates the table without remembering to also write the audit row.
• Database-level CDC (reading the write-ahead log or a logical-replication stream, e.g., via Debezium, entirely outside the application): captures every change regardless of which application code path made it (closing the "forgot to log it" gap), decoupled from the write's own transaction latency, but the history lives in a separate downstream system (a Kafka topic, a sink table) rather than directly queryable alongside the source table without additional plumbing.
• Append-only pattern (never UPDATE in place; every change is a new row, and "current" is whichever row for a given key has the latest timestamp or highest version number): the history IS the table, so there's no risk of forgetting to log a change, and querying "state as of time T" is a natural filter; the trade-off is that every ordinary read of "current state" now needs a filter or a materialized "current" view to avoid scanning the entire history.

Worked example

For created_by/created_at/updated_by/updated_at as always-present columns: these answer "who last touched this row and when" cheaply for the CURRENT row, but they overwrite prior history on every update, so they are not a substitute for any of the three mechanisms above if you need to see the full sequence of past changes, only a complement to them for the common, cheaper case of "who changed this most recently."

Trade-offs and pitfalls

• The trigger-based audit table is the most common production pattern because it's the most directly queryable, but it's also the easiest to accidentally bypass: a bulk UPDATE run directly against the database by an operator, or a data-migration script, can skip the application code path the audit-table logic assumed was the only writer, silently leaving a gap in the history.
• CDC closes that "forgot to log it" gap completely (it reads the actual write-ahead log, not application intent), which is exactly why it's the more robust choice when auditability is a compliance requirement rather than a convenience feature, at the cost of the history living in a separate system that needs its own query tooling.
• The append-only pattern's "no UPDATE ever" discipline is the strongest guarantee of the three (there is structurally no way to lose history, since nothing is ever overwritten), but it pushes real complexity onto every reader that wants current state, and it requires a clear convention (a version number, or a is_current flag maintained carefully) to avoid every query needing to compute "which row is the latest" itself.

Goal: keep fast point-selects but preserve scan throughput in columnar store.
Design choices:

• Maintain clustered sort keys so frequently point-selected columns (primary key or lookup key) are leading in sort order; this allows range reads to be localized.
• Use zone maps/min/max per segment to skip segments quickly for scans and to identify segments for point selects.
• Build a lightweight secondary index (sparse or bloom-filter index) mapping key -> segment/file offset to avoid scanning whole dataset for points; keep index stored row-oriented (or in separate KV engine) for constant-time lookups.
• Keep data partitioned by high-cardinality dimensions used in point selects (e.g., date or customer_id) so point queries target fewer segments.
• For frequently-accessed small subsets, maintain a small row-oriented cache/materialized table (hot-store) updated in near real-time; fall back to columnar for large scans.
  Trade-offs: secondary indexes and caches increase storage and write cost but give low-latency point queries while columnar layout remains optimized for scans. Monitor write amplification and tune compaction/partitioning.

Direct answer

An orders table repeating product_name on every row is a classic 3NF violation (a transitive dependency: product_name depends on product_id, which is itself not the table's key, rather than depending directly on order_id), not a 2NF violation, since 2NF specifically concerns partial dependencies on part of a composite key and order_id here is a single-column key with no composite-key partial-dependency issue to have; the fix is to move product_name into its own products table referenced by a foreign key. Whether to later denormalize it back for reporting is a separate, deliberate performance decision, not a sign the original normalization was wrong.

Structured elaboration

Normalizing: if orders(order_id, product_id, product_name, quantity, ...) repeats product_name for every order of the same product, that's a transitive dependency: order_id -> product_id -> product_name, where product_name depends on product_id, a non-key column, not on order_id (the primary key) directly. This is specifically a 3NF violation (every non-key attribute must depend on the key, the whole key, and nothing but the key; product_name fails the "nothing but the key" part via product_id). It is not a 2NF issue here: 2NF violations require a composite candidate key with a non-key attribute depending on only part of it, and order_id alone is the key of this table, so there's no partial dependency to violate in the first place. (If instead the table were an order-line-items table with composite key (order_id, product_id), then product_name depending only on product_id would be a 2NF violation instead, a partial dependency on part of the composite key; the two are distinct failure modes and which one applies depends on what the actual key is.) The fix either way: products(product_id, product_name), orders(order_id, product_id, quantity, ...), joining on product_id whenever the name is needed. This also fixes the underlying real-world problem the question describes (inconsistent product naming across orders): once product_name lives in exactly one place, there's exactly one place to correct a typo or a rename, and every order referencing that product automatically reflects the correction.

Reasons to intentionally denormalize back for reporting: once the normalized structure is correct, a read-heavy reporting table that flattens product_name back onto each order row is a legitimate, separate decision, made for query-performance reasons: a dashboard doing GROUP BY product_name across millions of order rows avoids a join entirely if the name is already flattened in, at the cost of needing to keep that flattened copy in sync whenever a product is renamed.

Worked example

The order of operations matters and is itself worth naming: fix the normalized source first (one products table, one authoritative name per product, orders referencing it by product_id), THEN build the denormalized reporting copy FROM that corrected source, refreshed on whatever cadence reporting needs. Denormalizing directly from the still-inconsistent original orders table would simply bake the same naming inconsistencies into the reporting copy, solving nothing.

Trade-offs and pitfalls

• The single most important sequencing point: normalize first, denormalize second, always from the corrected source; skipping straight to "let's flatten a reporting table" without first fixing the underlying inconsistency just produces a faster way to serve the same wrong data.
• Getting the normal-form diagnosis precise matters in practice, not just academically: correctly naming this a 3NF (transitive-dependency) issue rather than a 2NF (partial-dependency) issue is what tells you the fix is "move the non-key-dependent attribute to its own table," the same fix either way here, but the distinction matters on tables with genuinely composite keys, where misdiagnosing a 2NF partial dependency as a transitive one (or vice versa) can lead to normalizing the wrong column out.
• Once a denormalized reporting copy exists, it needs an explicit refresh mechanism (a scheduled ETL, a trigger, or CDC) to stay in sync with the normalized source whenever a product is renamed; without that, the reporting copy silently reintroduces exactly the kind of staleness/inconsistency risk normalizing the source was meant to eliminate.
• Not every reporting need actually justifies denormalizing; if the join from orders to products is cheap (a small products table, well-indexed), the reporting query may perform perfectly well against the normalized structure directly, and adding a denormalized copy "just in case" adds real maintenance cost for a performance problem that was never actually measured.

Direct answer

A course-enrollment system needs three tables: students, courses, and a junction table enrollments that resolves the many-to-many relationship, carrying enrollment_date and grade as attributes of the enrollment itself (not of either student or course alone).

Structured elaboration

• students(student_id, name, email, ...), courses(course_id, title, credits, ...).
• enrollments(student_id FK, course_id FK, enrollment_date, grade, PRIMARY KEY(student_id, course_id)): the composite primary key both resolves the many-to-many cardinality and structurally prevents a duplicate enrollment (the same student enrolling in the same course twice).
• Indexing at ~10M enrollments: the composite PK already gives you an efficient lookup path for "all courses a given student took" (leading column student_id). For the reverse direction, "everyone enrolled in a given course," add a secondary index on course_id alone, since the PK's B-tree is ordered by student_id first and won't serve that lookup efficiently at scale.

Worked example

CREATE TABLE students (
  student_id BIGINT PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE
);

CREATE TABLE courses (
  course_id  BIGINT PRIMARY KEY,
  title      TEXT NOT NULL,
  credits    SMALLINT NOT NULL
);

CREATE TABLE enrollments (
  student_id      BIGINT NOT NULL REFERENCES students(student_id),
  course_id       BIGINT NOT NULL REFERENCES courses(course_id),
  enrollment_date DATE NOT NULL,
  grade           CHAR(2),
  PRIMARY KEY (student_id, course_id)
);

CREATE INDEX idx_enrollments_course ON enrollments (course_id);

Verified against SQLite (a reasonable proxy for the relational logic; Postgres syntax is equivalent):

sqlite> INSERT INTO students VALUES (1,'Ana','ana@x.com');
sqlite> INSERT INTO courses VALUES (100,'Databases',3);
sqlite> INSERT INTO enrollments VALUES (1,100,'2026-01-10','A');
sqlite> INSERT INTO enrollments VALUES (1,100,'2026-01-11','B');
-- second insert correctly raises a UNIQUE constraint failure on the (student_id, course_id) primary key

This confirms the composite primary key is what prevents the duplicate-enrollment anomaly, not application-level checking alone.

Trade-offs and pitfalls

• At 10M rows, PRIMARY KEY (student_id, course_id) clustering the table by student_id means "all of a student's enrollments" is a cheap range scan, but "all students in course X" without the secondary index would force a full scan; always add the reverse-direction index explicitly rather than assuming the PK covers both directions.
• grade living on the junction table rather than a separate table is deliberate: a grade only makes sense in the context of a specific enrollment, so it is functionally dependent on the full composite key, not on either student_id or course_id alone (this is exactly the 2NF reasoning: don't let grade accidentally end up depending on just one half of the key).
• A tempting shortcut is a single surrogate enrollment_id as the primary key with a separate UNIQUE(student_id, course_id) constraint; that is equally correct and is often preferred if other tables need to reference a specific enrollment by a single-column foreign key, at the cost of one extra join to check whether a student is already in a course.