Diagnose warning signs of poor table design, such as repeating groups, mixed entity types, and partial-key dependencies, in a provided schema

Targeted Learning Outcomes

The example problems in this set give practice toward the following module outcome:

• MO3 [Analyze]: By the end of this module, students will be able to diagnose warning signs of poor table design, such as repeating groups, mixed entity types, and partial-key dependencies, in a provided schema.

Each problem asks you to examine a realistic table schema, identify which warning signs are present, and explain why each sign indicates a design problem. This targets the Analyze level of Bloom's Taxonomy — you must break the schema apart, distinguish its structural properties, and draw reasoned conclusions rather than simply recall definitions.

Problem 1: Spotting Repeating Groups

Outcome practised: MO3 [Analyze]

Problem Statement

A developer has created the following table to store customer orders for an online store:

Diagnose the warning sign(s) of poor table design present in this schema and explain the practical consequences.

Solution

1. Identify the primary key and the columns that repeat.

   The primary key is order_id. Examine the remaining columns: item1_name, item1_qty, item1_price, item2_name, item2_qty, item2_price, item3_name, item3_qty, item3_price. Notice that the same three attributes — name, qty, and price — appear three times, each time with a numeric suffix (1, 2, 3). This is the definition of a repeating group: a set of logically identical columns distinguished only by a positional number.

2. Name the warning sign explicitly.

   Repeating groups — the schema encodes a one-to-many relationship (one order can have many items) by adding numbered column sets instead of using a separate table. This violates First Normal Form (1NF), which requires that every column hold a single, atomic value and that there be no repeating column groups.

3. Describe the structural consequences.
   • Hard limit on items: The design allows at most 3 items per order. Adding a 4th item requires an ALTER TABLE statement, which is a schema change, not a data change.
   • Wasted space: An order with only 1 item still occupies columns for items 2 and 3, leaving them NULL.
   • Query complexity: To find all orders that include a particular product, a query must search across item1_name, item2_name, and item3_name with OR conditions instead of a single column scan.
   • Aggregation difficulty: Summing quantities or prices requires adding values from three separate columns rather than using a simple SUM() aggregate on one column.
4. State the corrective direction.

   The repeating group should be extracted into a child table, for example order_items(order_item_id, order_id, item_name, qty, price), with order_id as a foreign key back to the orders table. Each item becomes its own row, eliminating the hard limit and simplifying all queries.

Problem 2: Diagnosing Mixed Entity Types

Outcome practised: MO3 [Analyze]

Problem Statement

A school database stores the following information in a single table:

Diagnose the warning sign(s) of poor design and explain the anomalies that result.

Solution

1. Identify the distinct real-world entities represented in the table.

   Ask: "What things is this table actually describing?" Careful inspection reveals three separate real-world entities bundled together:

   • Student — described by student_id, student_first_name, student_last_name, student_email.
   • Course — described by course_id, course_title, course_credits, instructor_name.
   • Enrollment — the relationship between a student and a course, described by enrollment_id, enrollment_date, grade.
2. Name the warning sign explicitly.

   Mixed entity types — the table conflates three logically distinct entities (Student, Course, Enrollment) into one structure. A well-designed schema should have one table per entity, with relationship tables (like Enrollment) holding only the foreign keys and attributes that truly belong to the relationship itself.

3. Trace the update, insert, and delete anomalies that result.
   • Update anomaly: If the title of a course changes, every row in which that course_id appears must be updated. Miss even one row and the database contains contradictory course titles for the same course.
   • Insert anomaly: A new course cannot be recorded in the database until at least one student enrolls in it, because every row requires a student. Storing a course with no students forces NULL values into student columns, which is logically meaningless.
   • Delete anomaly: If the last student enrolled in a course drops it and their enrollment row is deleted, all knowledge of that course — its title, credits, and instructor — is permanently lost.
   • Data redundancy: A student enrolled in five courses will have their name and email duplicated across five rows, wasting storage and creating inconsistency risk if the email changes.
4. State the corrective direction.

   Separate the three entities into three tables: students(student_id, first_name, last_name, email), courses(course_id, title, credits, instructor_name), and enrollments(enrollment_id, student_id, course_id, enrollment_date, grade). The enrollments table holds foreign keys to both students and courses, plus only the attributes that genuinely belong to the enrollment event.

Problem 3: Identifying Partial-Key Dependencies

Outcome practised: MO3 [Analyze]

Problem Statement

A retail database uses the following table to track which products appear on which purchase orders. The composite primary key is (po_id, product_id).

Diagnose any partial-key dependencies and explain why they indicate poor design.

Solution

1. Restate the composite primary key and what a partial dependency means.

   The primary key is (po_id, product_id) — both columns together uniquely identify a row. A partial-key dependency exists when a non-key column can be determined by only part of the composite key, rather than requiring all parts. This violates Second Normal Form (2NF).

2. Test each non-key column against each part of the key.

   Ask: "To know this column's value, do I need both po_id and product_id, or just one of them?"

   Column	Depends on po_id alone?	Depends on product_id alone?	Depends on both?	Verdict
   quantity_ordered	No	No	Yes — how many of this product on this PO	Full dependency ✓
   unit_price_at_order	No	No	Yes — negotiated price for this product on this PO	Full dependency ✓
   product_name	No	Yes — a product has one name regardless of PO	No	Partial dependency ✗
   product_category	No	Yes — category belongs to the product, not the PO	No	Partial dependency ✗
   supplier_name	No	Yes — supplier is a property of the product	No	Partial dependency ✗
   po_date	Yes — a PO has one date regardless of which product	No	No	Partial dependency ✗
   po_status	Yes — status belongs to the PO, not a specific product line	No	No	Partial dependency ✗

3. Explain the consequences of each group of partial dependencies.
   • Product attributes (product_name, product_category, supplier_name) depend only on product_id: If product 42 appears on 500 purchase orders, its name and category are stored 500 times. Changing the product name requires updating 500 rows; missing any one row creates an inconsistency. A new product also cannot be stored until it appears on a PO (insert anomaly), and deleting all POs for a product erases the product's attributes (delete anomaly).
   • PO attributes (po_date, po_status) depend only on po_id: A purchase order with 20 line items stores the same date and status 20 times. Updating the status (e.g., from "Pending" to "Shipped") requires updating 20 rows atomically or risk inconsistent status values within the same PO.
4. State the corrective direction.

   Decompose the table to remove partial dependencies:

   • products(product_id, product_name, product_category, supplier_name) — product attributes move here.
   • purchase_orders(po_id, po_date, po_status) — PO-level attributes move here.
   • po_line_items(po_id, product_id, quantity_ordered, unit_price_at_order) — only fully dependent attributes remain in the junction table.

   Now every non-key attribute in every table depends on the whole key, satisfying 2NF.

Problem 4: Multi-Sign Diagnosis — All Three Warning Signs Together

Outcome practised: MO3 [Analyze]

Problem Statement

A small business uses a single spreadsheet-turned-database table to manage its operations. Examine the schema below and diagnose all warning signs of poor design that are present, citing evidence from the schema for each one.

Solution

1. Scan for repeating groups.

   Look for columns with numbered suffixes that encode the same logical concept multiple times:

   • service1_description, service2_description, service3_description
   • service1_hours, service2_hours, service3_hours
   • service1_rate, service2_rate, service3_rate

   Warning sign confirmed: Repeating groups. The three attributes description, hours, and rate repeat three times with numeric suffixes. This caps an invoice at exactly 3 line items, wastes space when fewer than 3 services are billed, and forces multi-column OR searches to find all invoices for a given service type.

2. Scan for mixed entity types.

   Identify the distinct real-world things described by the columns:

   • Client entity: client_id, client_name, client_email, client_billing_address — these describe a client, not an invoice.
   • Invoice entity: invoice_id, invoice_date, payment_status, tax_rate — these describe the invoice transaction.
   • Invoice line item entity (buried in repeating groups): serviceN_description, serviceN_hours, serviceN_rate — these describe individual service charges.

   Warning sign confirmed: Mixed entity types. Three distinct entities (Client, Invoice, Invoice Line Item) are collapsed into one table. This causes update anomalies (changing a client's email requires finding and updating every invoice row for that client), insert anomalies (a new client cannot be stored without an invoice), and delete anomalies (deleting the last invoice for a client erases all client contact information).

3. Check for partial-key dependencies.

   The primary key is the single column invoice_id. Partial-key dependencies require a composite primary key, so in their classic form they do not technically apply here. However, note that client_name, client_email, and client_billing_address are functionally determined by client_id alone — not by invoice_id. This is a transitive dependency (a closely related design flaw), where a non-key column (client_name) depends on another non-key column (client_id) rather than directly on the primary key. This is the root structural cause of the mixed-entity-type anomalies identified in Step 2.

4. Summarise all warning signs found.

   Warning Sign	Evidence in Schema	Key Consequence
   Repeating groups	service1_*, service2_*, service3_* column sets	Hard cap on line items; complex queries; NULL waste
   Mixed entity types	Client attributes and Invoice attributes in same table	Update, insert, and delete anomalies; data redundancy
   Transitive dependency	client_name/email/address determined by client_id, not invoice_id	Client data duplicated across every invoice row

5. State the corrective direction.

   Decompose into three tables:

   • clients(client_id, client_name, client_email, client_billing_address)
   • invoices(invoice_id, client_id, invoice_date, payment_status, tax_rate)
   • invoice_line_items(line_item_id, invoice_id, service_description, hours, rate)

   This eliminates all three warning signs: repeating groups become rows in invoice_line_items; entity types are separated; and client attributes live only once in the clients table.