Reading 3: Designing a Good Database

Design Matters

Knowing that a relational database organizes data into relations (tables), where each row is a tuple and each column is an attribute, is a good start. But knowing the vocabulary does not tell you how to design a database well. And the design matters enormously — a poorly designed database can be slow, inconsistent, and surprisingly fragile in ways that are not obvious until something goes wrong.

The most common design mistake in relational databases is redundancy: storing the same piece of information in more than one place. This reading explores why redundancy is a problem, what can go wrong because of it, and how thoughtful design eliminates it.

The Problem with Redundancy

Let's work through an example. Imagine a high school is building a database to track student course enrollments. A first attempt might produce the following single relation, called ENROLLMENT:

At first glance, this looks reasonable — everything you might want to know about an enrollment is in one row. But look more carefully and you will see the redundancy. Priya Adeyemi's name and grade level appear in two tuples. If Priya is enrolled in six courses, her name and grade appear six times. Now multiply that by 500 students each enrolled in six courses — that's 3,000 tuples, each containing redundant student information.

The same redundancy applies to course information. American Literature's name, department, and credit value appear once for every student enrolled in it. If 80 students take American Literature, those three attributes are repeated 80 times.

This redundancy creates three serious problems.

Problem 1: Update Anomalies

Suppose Priya Adeyemi advances to 11th grade and her grade attribute needs to be updated. In this design, her grade appears in every tuple for every course she is enrolled in. If the person making the update changes it in five of her six enrollment records but misses one, the database now contains inconsistent data — five tuples say grade 11, one still says grade 10. Which is correct? The database cannot tell you.

Problem 2: Deletion Anomalies

Suppose Earth Science (SCI205) is cancelled as a course offering. To remove it from the database, you would delete all tuples containing Course ID SCI205. But if Marcus Hernandez is the only student enrolled in Earth Science, deleting that tuple also deletes Marcus's record entirely — you have lost information about a student while trying to remove information about a course. The two concepts are tangled together in the same tuple.

Problem 3: Insertion Anomalies

Suppose the district adds a new course, AP Computer Science, to the catalog for next semester, but no students have enrolled yet. In this design, you cannot add AP Computer Science to the database at all — because a tuple requires both a Student ID and a Course ID, and there is no student to attach to the new course yet. The course cannot exist in the database until at least one student enrolls, which is backwards from how a school actually operates.

The Solution: One Concept Per Relation

The root cause of all three problems is that our single ENROLLMENT relation is trying to represent three distinct concepts simultaneously: information about students, information about courses, and the relationship between students and courses (which student is taking which course, and when).

The solution is to separate these concepts into individual relations, one per concept:

Now let's check whether the three problems have been resolved:

Update anomalies: Priya's grade level appears exactly once, in the STUDENT relation. Updating it requires changing one tuple in one place. There is no risk of inconsistency.

Deletion anomalies: Removing Earth Science means deleting the SCI205 tuple from the COURSE relation and deleting the corresponding tuples from the ENROLLMENT relation. Marcus Hernandez's record in the STUDENT relation is completely unaffected — the student and the course are stored independently.

Insertion anomalies: AP Computer Science can be added to the COURSE relation the moment the district decides to offer it, with no students attached. It exists in the database as soon as a curriculum administrator creates the record. Students can be linked to it via the ENROLLMENT relation when they register.

All three problems disappear when we give each concept its own relation.

How the Relations Are Linked

You might wonder: if student information and course information are in separate relations, how do we answer questions that need both? How do we find out which courses Priya is taking? Or which students are enrolled in American Literature?

The answer is the shared identifier. Notice that the ENROLLMENT relation contains Student ID and Course ID — the same identifiers that serve as the unique key in the STUDENT and COURSE relations respectively. These shared identifiers are the links between relations.

To find all the courses Priya Adeyemi (Student ID 10042) is taking, you would:

1. Look up Student ID 10042 in the ENROLLMENT relation to find all her Course IDs (ENG401 and MTH302 in our example).
2. Look up those Course IDs in the COURSE relation to get the full course details.

The DBMS handles this linking automatically when you ask a question. In Topic 5c, we will look at exactly how this is expressed using database query operations — including the JOIN operation, which is the formal mechanism for combining information from multiple relations in response to a single question.

For now, the key insight is this: relations are connected through shared identifiers, and a well-designed database stores each concept exactly once, letting the links between relations do the work of connecting them.

A Design Principle to Carry Forward

Database designers have formalized the ideas in this reading into a set of mathematical rules called normal forms. You do not need to know the formal rules — database professionals spend entire courses on them — but the underlying intuition can be stated simply:

When you find yourself putting more than one concept into a single relation — students and courses, employees and departments, books and authors — that is a signal to split. When you find the same information repeated in multiple tuples, that is redundancy, and redundancy is the source of update, deletion, and insertion problems.

Good database design is ultimately about recognizing the distinct concepts in the real world you are trying to model and giving each one its own properly structured home in the database.

Now You Try

Examine the following single-relation database design, then answer the questions. A suggested response is in the answer key.

1. Identify two specific examples of redundancy in this relation.
2. Describe a deletion anomaly that could occur in this design.
3. Describe an insertion anomaly that could occur in this design.
4. Redesign this database as three separate relations. Name each relation and list its attributes. Make sure each relation represents exactly one concept and contains no redundant information.