Questions: Database Schema Design

Question 1

A developer argues that validating email uniqueness only in application code is sufficient — no UNIQUE constraint needed in the schema. What is the critical flaw in this reasoning?

• A. Nothing — application-layer validation is more flexible and equally reliable
• B. Application code cannot enforce uniqueness as efficiently as the database engine
• C. Two concurrent transactions can both pass the application check before either inserts, creating a duplicate the code never detects
• D. Database constraints are only needed for NoSQL systems, not relational ones

Answer: (C)

Application-level checks have a race condition: two concurrent requests can both read 'email not found,' both pass validation, and both insert — creating a duplicate. A UNIQUE constraint is enforced atomically by the database engine and cannot be circumvented by concurrency or bugs in application code. Schema constraints are the last line of defense against data corruption.

Question 2

A system has Students and Courses where each student can enroll in many courses and each course can have many students. How should this relationship be represented?

• A. Add a courses column (comma-separated list) to the Students table
• B. Add a students column (as an array) to the Courses table
• C. Create a junction table (e.g., Enrollments) with foreign keys referencing both Students and Courses
• D. Add a student_id foreign key directly to the Courses table

Answer: (C)

Many-to-many relationships require a junction table containing foreign keys to both participating tables. Storing lists in a single column (options A and B) violates first normal form and makes querying, joining, and enforcing referential integrity impossible. A one-directional foreign key (option D) models a one-to-many relationship, not many-to-many.

Question 3

A fully normalized schema minimizes redundancy but may require expensive joins for common queries, making deliberate denormalization a valid design tradeoff in practice.

• T. True
• F. False

Answer: True

Normalization and query performance are in tension. In practice, experienced schema designers normalize first and then selectively denormalize hot paths — for example, storing a user's display name directly on the orders table to avoid a join on every order listing. The discipline is to document every denormalization decision and accept its maintenance cost: when the source data changes, it must be updated in multiple places.

Question 4

Because schemas can usually be altered later with ALTER TABLE, it is safe to defer schema design decisions until after the application reaches production.

• T. True
• F. False

Answer: False

Schema changes on tables with millions of rows are expensive, slow, and risky in ways that application code refactoring is not. Adding a NOT NULL column requires backfilling every existing row; changing a data type may require rewriting indexes and running migrations during a maintenance window. Schema design deserves more upfront investment than almost any other software design decision — it is the hardest thing to change after the fact.

Question 5

Explain why enforcing data integrity at the schema level is preferable to relying solely on application code, and give one concrete example.

Model answer: Schema constraints (NOT NULL, UNIQUE, CHECK, FK) are enforced by the database engine unconditionally — no application code path, script, or bulk import can bypass them. Application code has bugs, race conditions, and multiple entry points that may skip validation. Example: a UNIQUE constraint on an email column prevents duplicate accounts even if two concurrent registration requests both pass the app-level check simultaneously.

The principle is defense in depth: application code is fallible, but schema constraints are enforced at the storage layer and cannot be circumvented. Every constraint skipped at the schema level is a potential data quality bug waiting to surface.