Common Database Design Mistakes

By this point in the course, you've done the hard theoretical work: relational constraints, normalization through Third Normal Form, joins and user views, and physical design. You know what a well-designed database looks like. This module asks a different, more practical question: what actually goes wrong, in real projects, when that theory meets deadlines, incomplete requirements, and developers who'd rather start coding than keep planning?

The previous modules in this course walked through the logical design, physical design, and, to a lesser extent, implementation stages of the Database Life Cycle (DBLC). This module doesn't introduce new design theory so much as it holds a mirror up to how that theory actually gets misapplied — identifying the mistakes that show up again and again across real database projects, and what to do instead.

Design mistakes tend to cluster around a recurring set of areas, and this module works through them one at a time: mistakes involving business objects^[1] and business rules^[2]; mistakes at the column^[3] level; mistakes involving constraints and keys, including primary and foreign keys specifically; mistakes around relationships^[4] and referential integrity^[5]; and mistakes that only surface once a database has to handle international issues^[6] — different alphabets, writing systems, and data-formatting conventions. The module closes by walking through how to actually evaluate a relational database against these mistakes, rather than just recognizing them in the abstract.

After completing this module, you should be able to:

1. Identify the main areas where database design mistakes commonly occur
2. Describe mistakes associated with business objects and business rules
3. Describe mistakes associated with columns
4. Describe mistakes associated with constraints and keys
5. Describe mistakes associated with relationships and referential integrity
6. Describe mistakes associated with international issues
7. Interpret the statement: "There are no wrong databases, just useless ones."

That last objective is worth sitting with before you move on. A database can be perfectly normalized, indexed correctly, and technically sound — and still fail, if it doesn't actually solve the problem it was built for. Most of the mistakes in this module aren't syntax errors. They're the result of skipping the steps that make sure a technically correct design is also a useful one.

Database design is often one of the very first steps in a development project, and that timing creates a real temptation: developers want to rush ahead and start writing code. Shipping code is visible progress you can show management. Writing application software that maps to your ERD is more fun than working through plans, use cases, and documentation — all the unglamorous groundwork that has to happen before you can roll up your sleeves and actually build something.

That temptation is exactly what causes some of the worst design mistakes in this module. Before you start creating tables and writing code, there's real homework to do:

1. Make sure you actually understand the problem.
2. Write requirements documents that state the problem clearly.
3. Build use cases to confirm you've actually solved it.
4. Design a solution.
5. Test that design against the use cases you built.
6. Document everything.

Skip any one of these steps, and the mistakes covered in the rest of this module become far more likely — not because you don't know the theory, but because you never gave yourself the chance to apply it deliberately.

Documentation is technically part of preparation, but it's important and underappreciated enough to earn its own section. Plenty of developers think of documentation as busywork — a chore that exists mainly to satisfy managers, while the real work happens in building elegant, intricate data structures. That attitude is occasionally convenient. It's also wrong about what documentation is actually for.

The real purpose of documentation is to keep everyone on a project pointed at the same goals. It should tell people where the project is headed and spell out the design decisions behind it, so everyone understands how the pieces are meant to fit together. When documentation is weak or missing, different people quietly make different, often contradictory assumptions about how the system works. Eventually those assumptions collide. Resolving the collision means developers going back to redo work built on the wrong assumptions — which means more work, more errors, and no shortage of arguing over whose fault it was.

The real fault, almost always, was poor documentation in the first place.

Adapted in part from Rod Stephens, Beginning Database Design Solutions (Wiley).

The next lesson takes a closer look at the specific areas where these mistakes tend to show up, starting with a broader overview before working through business rules, columns, keys, relationships, and international design issues one at a time.

[1] Business objects: Items in a business environment that are related, and about which data need to be stored (e.g., customers, products, orders, etc.)

[2] Business rules: A set of rules or conditions describing the business policies that apply to the data stored on a company's databases.

[3] Column: Part of the structure of a database table; also known as a field or, during early database design, as an attribute.

[4] Relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.

[5] Referential integrity: The means of maintaining integrity of data between one or more related tables — specifically, that a column of data in one table has a null or matching value in a corresponding table. Referential integrity is usually enforced with foreign keys.

[6] International issues: Database design considerations arising from other countries' differing data standards, alphabets, and writing systems.