Problems with Poorly Structured Tables

When a database table is designed without careful thought about what it truly represents, a cascade of practical problems follows. These problems are not merely theoretical inconveniences — they affect every application that reads from or writes to the database, they consume extra storage, they demand extra maintenance effort, and they erode confidence in the data itself. Understanding these problems in concrete terms is the essential first step toward designing databases that are reliable, efficient, and easy to maintain. The problems cluster around a central theme: a poorly structured table tries to represent more than one real-world concept at the same time, and that mixing of concerns creates friction at every operation — inserting new data, updating existing data, and deleting records.

To make the discussion concrete, consider a single table used to track which students are enrolled in which courses, along with information about the instructor of each course. Suppose the table looks like this:

Enrollment
+------------+------------------+-------------------+------------------+--------------------+
| StudentID  | StudentName      | CourseID          | CourseName       | InstructorName     |
+------------+------------------+-------------------+------------------+--------------------+
| S001       | Alice Nguyen     | CS101             | Intro to CS      | Dr. Patel          |
| S002       | Bob Martinez     | CS101             | Intro to CS      | Dr. Patel          |
| S003       | Carol Smith      | CS101             | Intro to CS      | Dr. Patel          |
| S001       | Alice Nguyen     | MATH201           | Calculus II      | Prof. Chen         |
| S004       | David Kim        | MATH201           | Calculus II      | Prof. Chen         |
+------------+------------------+-------------------+------------------+--------------------+

This table will serve as a running example throughout this topic. It looks harmless at first glance — all the relevant information appears to be present and readable. But as you examine it closely, serious structural flaws emerge.

Data Redundancy in Poorly Structured Tables

Redundancy means that the same fact is stored in more than one place. In the Enrollment table above, the fact that course CS101 is called "Intro to CS" and is taught by Dr. Patel appears in three separate rows — once for each student enrolled in that course. Similarly, Alice Nguyen's name is stored twice, once for each course she is enrolled in. This repetition is the hallmark of a poorly structured table.

Redundant data wastes storage space. In a small example this seems trivial, but consider a university with 20,000 students, each enrolled in an average of five courses. Every piece of course information — course name, instructor name, department, credit hours — would be duplicated 20,000 times or more across the table. The wasted space is real and measurable.

More importantly, redundancy makes the database much harder to maintain. If you want to record the fact that Dr. Patel has retired and been replaced by Dr. Lopez as the instructor for CS101, you must find and update every single row that mentions Dr. Patel in connection with CS101. In a large table, that means hunting through potentially thousands of rows. If even one row is missed, the table now contains contradictory information — some rows say Dr. Patel, others say Dr. Lopez — and there is no way for the database itself to know which version is correct.

Redundancy is also a diagnostic signal. When you notice that the same combination of values appears over and over in a table, it almost always means the table is conflating two or more distinct real-world concepts. In this example, the table is simultaneously describing students, courses, and the relationship between students and courses. Each of those is a separate concept that deserves its own table. When they are merged, redundancy is the unavoidable result.

Insertion Anomalies

An insertion anomaly occurs when you cannot record a piece of information in the database without also supplying other, unrelated information that you do not yet have — or that does not logically belong alongside it. These anomalies arise directly because unrelated facts have been bundled together in the same table.

Consider the Enrollment table again. Suppose the university creates a brand-new course, CS202 Advanced Programming, to be taught by Dr. Rivera, but no students have enrolled yet. Can this fact be recorded? If you try to insert a row, you immediately hit a wall:

INSERT INTO Enrollment VALUES (???, ???, 'CS202', 'Advanced Programming', 'Dr. Rivera');
-- What do you put for StudentID and StudentName?
-- There are no students yet!

You have two bad options. First, you could simply not record the course at all, meaning the database does not reflect the reality that the course exists. Second, you could insert a fake, placeholder student — perhaps a row with StudentID set to NULL or a dummy value like 'NONE' — just to satisfy the requirement that every row have student data. Both options are wrong. The first hides real information. The second pollutes the table with fictional data and creates its own complications downstream.

The root cause is that the table forces a dependency between two concepts that are genuinely independent: a course can exist without any students enrolled in it. When the table design ties the existence of a course record to the existence of an enrollment record, it prevents the database from accurately reflecting real-world states. An empty classroom is a real thing. A course with no current students is a real thing. A well-structured schema can represent these states naturally; a poorly structured one cannot.

Insertion anomalies also appear from the other direction. Suppose a new student, Eva Torres (S005), registers at the university but has not yet chosen any courses. You cannot record Eva's existence in the Enrollment table without also specifying a course — again requiring a dummy value or leaving the student completely invisible to the database.

Update Anomalies

An update anomaly is a direct consequence of data redundancy. The logic is straightforward: if the same fact is stored in N rows, then updating that fact correctly requires changing all N rows simultaneously and atomically. If any rows are missed — due to human error, a bug in an update query, or a partial transaction failure — the database ends up in an inconsistent state where different rows report different values for what should be a single, consistent fact.

Returning to the example: suppose Prof. Chen leaves the university and MATH201 is taken over by Dr. Okafor. The correct update requires touching every row that references MATH201:

UPDATE Enrollment
SET InstructorName = 'Dr. Okafor'
WHERE CourseID = 'MATH201';

If this update runs successfully on all relevant rows, the table is momentarily consistent. But notice what has to happen: the database engine must scan the entire table looking for every row with CourseID = 'MATH201'. As the number of enrolled students grows, this scan grows with it. The effort required to update a single real-world fact — who teaches a course — scales linearly with the number of enrollments. In a well-structured database, changing the instructor of a course would require updating exactly one row in a dedicated Courses table.

Now consider what happens if the update query has an error, or if a developer updates only some rows manually:

Enrollment (after partial update)
+------------+------------------+-------------------+------------------+--------------------+
| StudentID  | StudentName      | CourseID          | CourseName       | InstructorName     |
+------------+------------------+-------------------+------------------+--------------------+
| S001       | Alice Nguyen     | MATH201           | Calculus II      | Dr. Okafor         |  ← updated
| S004       | David Kim        | MATH201           | Calculus II      | Prof. Chen         |  ← NOT updated
+------------+------------------+-------------------+------------------+--------------------+

The table now contains contradictory information. If you query "Who teaches MATH201?", the answer depends on which row the query happens to look at. Some application logic might return "Dr. Okafor", other logic might return "Prof. Chen", and queries that return both rows will show both names — neither of which can be trusted as definitively correct. The database has lost its status as a single, authoritative source of truth for this fact.

Partial updates are more common than developers expect. Bulk updates can fail midway through. Developers working in different parts of a system may update rows through different code paths, each of which misses some rows. The more redundancy exists in a schema, the higher the probability of a partial update occurring, and the harder it is to detect and repair the resulting inconsistency.

Deletion Anomalies

A deletion anomaly occurs when deleting one piece of information causes the unintentional and permanent loss of a different, unrelated piece of information. Deletion anomalies reveal one of the most fundamental structural mistakes in table design: two distinct entities being merged into a single row.

In the Enrollment table, consider what happens if David Kim (S004) withdraws from MATH201. You want to record that David is no longer enrolled in that course, so you delete the relevant row:

DELETE FROM Enrollment
WHERE StudentID = 'S004' AND CourseID = 'MATH201';

This single deletion removes David's enrollment record — which is the intended effect. But look at what else disappears: if David Kim was the only student enrolled in MATH201, this deletion also destroys the only record that MATH201 exists at all, that it is called "Calculus II", and that it is taught by Prof. Chen. Information about the course — a genuinely separate real-world entity — has been permanently lost as a side effect of recording a student's withdrawal.

This is not a recoverable situation unless the database has a backup or audit log. The course information is simply gone. If a new student wants to enroll in MATH201 the next day, there is no longer any record of what that course is or who teaches it.

Deletion anomalies always indicate that two distinct entities — in this case, a course and an enrollment event — have been incorrectly merged into a single table row. A well-structured schema would store course information in a dedicated Courses table, completely independent of enrollment records. Deleting an enrollment would have no effect whatsoever on the Courses table.

Recognizing which kinds of deletions in a schema trigger unintended data loss is one of the most important diagnostic skills in database design. A useful technique is to ask: "If I delete every row associated with entity X, does information about some other entity Y also disappear?" If the answer is yes, the schema is conflating two distinct concepts and should be redesigned.

Inconsistent Data as a Consequence

Inconsistency is the accumulated result of the anomalies described above. Once a database has experienced even one partial update, one deletion anomaly, or one insertion of placeholder data, it may no longer contain a single, authoritative version of the truth for the affected facts. Different parts of the database tell different stories, and there is often no automated way to determine which story is correct.

The practical consequences for applications are serious. Consider an application that generates a class roster by querying the Enrollment table. If some rows say the instructor is "Prof. Chen" and others say "Dr. Okafor" for the same course, the roster might display both names, or it might display whichever name appears in the first row returned — which depends on the physical order of rows in the table, something that is not guaranteed to be stable. Either way, the output is wrong, and the wrongness may not be immediately obvious to the user.

Inconsistency is particularly insidious because it is difficult to detect after the fact. There is no built-in database alarm that says "these two rows contradict each other." Detection requires either careful auditing logic written into the application, or manual review by someone who knows what the data should look like. In large databases with millions of rows, comprehensive manual review is not feasible. Automated detection requires knowing exactly what constraints should hold — and writing those checks takes time and expertise that might have been better spent designing the schema correctly from the start.

Once inconsistency spreads — through automated processes that read incorrect data and use it to generate new records, through exports to downstream systems, or through caches that store stale values — it becomes extraordinarily difficult to repair. Each inconsistency may have propagated to dozens of dependent records. Repairing the database requires tracing those propagation paths, which may not even be fully documented.

Identifying a Poorly Structured Schema

Given the severe problems that poorly structured tables cause, it is valuable to be able to recognize warning signs in a schema before those problems fully materialize. Several patterns reliably indicate structural problems.

Repeating groups are one of the clearest warning signs. A repeating group occurs when a table contains multiple columns that represent the same kind of information, differentiated only by a number or index. For example:

Orders
+----------+------------+---------+---------+---------+
| OrderID  | CustomerID | Item1   | Item2   | Item3   |
+----------+------------+---------+---------+---------+
| O001     | C101       | Widget  | Gadget  | NULL    |
| O002     | C102       | Gizmo   | NULL    | NULL    |
+----------+------------+---------+---------+---------+

This design has obvious problems. What if an order has four items? You either reject the order or add a fourth column — and then a fifth, sixth, and so on. The schema cannot gracefully accommodate variable-length data. The solution is always to move the repeating data to a separate, related table: an OrderItems table with one row per item per order.

Mixed entity types are another major warning sign. When you look at the columns of a table and find that some columns describe one kind of thing while other columns describe a completely different kind of thing, anomalies are almost guaranteed. In the Enrollment table, StudentName describes a person, while CourseName and InstructorName describe a course. A well-structured schema separates these into a Students table, a Courses table, and an Enrollment table that links them together.

A useful diagnostic question is: What is one row in this table actually describing? If you cannot give a single, clear answer — if a row is simultaneously about a student AND a course AND an enrollment event — the table is trying to serve multiple purposes and should be decomposed.

Columns that are frequently NULL are a third warning sign, though a subtler one. When many rows in a table have NULL for certain columns, it often means the table is being used to represent multiple different types of entities, only some of which need those columns. For example:

People
+--------+------------------+----------------+--------------------+
| ID     | Name             | StudentGrade   | InstructorOffice   |
+--------+------------------+----------------+--------------------+
| P001   | Alice Nguyen     | A              | NULL               |
| P002   | Dr. Patel        | NULL           | Room 204           |
| P003   | Bob Martinez     | B+             | NULL               |
+--------+------------------+----------------+--------------------+

Here, StudentGrade is always NULL for instructors, and InstructorOffice is always NULL for students. The table is conflating two distinct entity types — students and instructors — into a single structure. This leads to wasted storage for all the NULLs, potential confusion in queries, and the same anomaly risks described throughout this topic.

Together, these warning signs — redundant values across rows, mixed entity types in a single table, repeating column groups, and pervasive NULLs — form a practical checklist for diagnosing poorly structured schemas. Each warning sign points toward the same underlying problem: the table is doing too much, representing too many real-world concepts, and paying the price in anomalies, inconsistency, and maintenance burden.