Features of a Good Relational Database Design

A good relational database design is a schema that minimizes redundancy, preserves data integrity over time, and avoids insertion, update and deletion anomalies. Each fact is stored in one place and referenced elsewhere by key, so changing it means updating a single row rather than finding and fixing every duplicate copy.

4 min read

A good relational database design isn't just "a schema that technically works." It's one that avoids redundant data, preserves data integrity as the database is used over time, and doesn't force awkward workarounds just to insert, update, or delete a single fact.

What makes a design "good"

Minimal redundancy. The same fact shouldn't be stored in multiple places. If a customer's email is stored once, on the customers table, and referenced everywhere else through customer_id, there's exactly one place to update it. If it's copied into every order row instead, updating it means finding and fixing every duplicate, and missing even one leaves the data inconsistent.

No anomalies. A poorly normalized design creates three classic problems, together called anomalies:

  • Insertion anomaly: you can't add certain data without also having unrelated data you may not have yet. If course information is only stored alongside enrolled students, you can't add a new course until at least one student enrolls in it.
  • Update anomaly: a single logical fact is duplicated across multiple rows, so updating it means updating every copy, and missing one leaves the data inconsistent.
  • Deletion anomaly: deleting one piece of information accidentally destroys another, unrelated piece of information stored alongside it. If a table only tracks "last order per customer" and a customer's only order is deleted, information about that customer disappears too.

Preserved dependencies. Every non-key attribute should genuinely depend on the whole key, not just part of it, and shouldn't depend on another non-key attribute instead. This is the underlying logic behind normalization (1NF through BCNF).

Lossless decomposition. If a table gets split into multiple smaller tables for better design, joining those tables back together should reconstruct exactly the original data, with no information lost and no spurious extra rows introduced.

A worked example

Consider a single flat table tracking student course enrollments:

student_idstudent_namecourse_idcourse_nameinstructor
1Asha101DatabasesDr. Rao
1Asha102NetworksDr. Lee
2Ravi101DatabasesDr. Rao

This design has real problems:

  • Redundancy: "Asha" is stored twice, and "Databases" / "Dr. Rao" is stored twice. If Dr. Rao is reassigned to a different course, every row for that course needs updating.
  • Insertion anomaly: a new course can't be added to the database until at least one student enrolls in it, since course data only exists attached to an enrollment row.
  • Deletion anomaly: if Ravi drops course 101 and that was his only enrollment, deleting that row also deletes all information about him as a student.
  • Update anomaly: correcting a course name means finding and updating every single row that mentions it.

A better design splits this into three related tables, students, courses, and enrollments (a linking table), each holding exactly one kind of fact, connected through foreign keys. Now a course can exist with zero enrollments, a course name only needs updating in one place, and deleting an enrollment never destroys unrelated student or course information.

Where this matters in real systems

Anomalies like these are exactly what normalization (covered in the 1NF through BCNF topics) is a formal process for eliminating. Real production schemas that skip this discipline tend to accumulate subtle data-integrity bugs over time, values silently drifting out of sync across duplicated copies, that are genuinely difficult to detect and even harder to clean up once a large amount of real data has been written against a flawed structure.

What interviewers actually ask about good database design

  • "What are the three classic anomalies a poor schema design can cause?" Insertion, update, and deletion anomalies, each explained with what actually goes wrong.
  • "Give an example of an update anomaly." Any scenario where a fact is duplicated across multiple rows, and updating it correctly requires finding and fixing every single copy.
  • "What does 'lossless decomposition' mean?" Splitting a table into smaller related tables such that joining them back together reconstructs the original data exactly, with nothing lost or spuriously added.
  • "How does normalization relate to avoiding these anomalies?" Normalization is the formal, rule-based process (1NF through BCNF and beyond) for restructuring a schema specifically to eliminate redundancy and the anomalies it causes.

Key takeaways

  • A good relational design minimizes redundancy and avoids insertion, update, and deletion anomalies.
  • An insertion anomaly blocks adding valid data because unrelated data isn't available yet; an update anomaly requires fixing duplicated data everywhere it appears; a deletion anomaly destroys unrelated information as a side effect.
  • Splitting a poorly designed flat table into properly related tables, each holding one kind of fact, fixes all three anomaly types.
  • Decomposition should be lossless: joining split tables back together should reconstruct the original data exactly.
All 174 DBMS interview questions

Log in to save which topics you've read across every subject.

Frequently asked questions

What are the three classic anomalies a poor schema design can cause?

Insertion, update, and deletion anomalies, each explained with what actually goes wrong.

Give an example of an update anomaly.

Any scenario where a fact is duplicated across multiple rows, and updating it correctly requires finding and fixing every single copy.

What does 'lossless decomposition' mean?

Splitting a table into smaller related tables such that joining them back together reconstructs the original data exactly, with nothing lost or spuriously added.

How does normalization relate to avoiding these anomalies?

Normalization is the formal, rule-based process (1NF through BCNF and beyond) for restructuring a schema specifically to eliminate redundancy and the anomalies it causes.