Common Database Design Mistakes

Every company and organization is different, and designing a relational database for one requires real research and attention to detail — enough to make sure the design actually meets that client's needs and performs efficiently under real workloads. But despite how different individual databases are from each other, the mistakes that show up while designing them are surprisingly consistent. Nearly all of them fall into one of four areas:

1. Business objects and rules
2. Constraints, columns, and keys
3. Relationships and referential integrity
4. International issues

The rest of this module works through each of these areas in turn. This lesson steps back first, to look at why design mistakes happen at all — and at one specific, deceptively small mistake that quietly makes every other mistake in this module worse: poor naming.

Most of this course so far has been about technical rules: normalizing tables, writing the SQL to create them, defining constraints correctly. That's necessary knowledge, but it's not the whole picture. In this module, we take a step back from the mechanics and look at the bigger question: what should you actually be thinking about when you sit down to design a database in the first place?

The mistakes covered here aren't a replacement for the normalization and design habits you've built up throughout this course and the one before it. Think of them instead as the other half of the picture — the habits and the mistakes working together. Good normalization technique won't save a database whose business rules were misunderstood from the start, and a clear understanding of the business won't save a database whose keys and constraints are a mess. You need both.

If you work with relational database management system (RDBMS) application programs day to day, you might reasonably wonder why database design deserves its own module at all. Most programs ship with sample databases you can copy and adapt, and it's often possible to borrow whole tables from one sample database and drop them into another you're building. Plenty of tools will even walk you step by step through defining and creating tables, click by click.

Here's the distinction that matters, though: none of those tools actually design a database for you. They help you create the physical tables that end up in a database — the columns, the data types, the constraints as you specify them. But whether those tables, columns, and relationships are the right ones for your problem is a decision the tool has no opinion on. A wizard will happily help you build a technically valid table that solves the wrong problem, just as easily as it'll help you build one that solves the right problem. The tool executes your design; it doesn't supply one.

With modern database tools, just about anyone can build a database. The real question is whether the database you end up with is actually useful — and that's a much narrower target than "technically functional."

A database isn't useful if you can't get data out of it quickly, reliably, and consistently. It isn't useful if it's full of incorrect or contradictory data — data you can retrieve instantly but can't trust. And it isn't useful if it gets stolen, lost, or quietly corrupted by a write that was only half-finished when the system crashed. Modern tools, a solid design, and a reasonable amount of common sense can address every one of these risks — but only if you actually understand what the risks are well enough to design around them, rather than discovering them after the fact.

That's really step one in building a useful database: understanding what "useful" means before you start. What should a database actually do for the people relying on it? What problems is it meant to solve, and which failure modes does it need to avoid? Working with a powerful database tool without clear goals is a lot like flying a plane through clouds with no compass — you have every capability you need and no sense of which direction to point it. This module exists to give you that sense of direction: by looking at the ways databases go wrong, it also defines, by contrast, what a genuinely good database looks like.

Set databases aside for a moment and think about software design in general. Design is what lays out a system's overall structure and the direction its future development will take. It decides which parts of a system talk to which other parts, and which subsystems exist to support the rest of the application. Get the design right, and everything built on top of it has a stable foundation. Get it wrong, and that instability doesn't stay contained.

A flawed underlying design doesn't just cause one isolated problem — it propagates. Bad assumptions baked into the design creep into the application's lowest-level code, producing flawed subsystems. Higher-level systems then get built on top of those already-flawed subsystems, inheriting the same bad assumptions, and their code becomes corrupted in turn. It's a chain reaction, not a single point of failure.

Often, this kind of decay spreads through an entire system quietly, and nobody notices until surprisingly late in the project. The longer development continues on a flawed foundation, the more entrenched the incorrect assumptions become, and the more reluctant everyone gets to suggest scrapping the design and starting fresh — because by then, that fresh start looks enormous. The longer a design problem sits unaddressed, the harder and more expensive it becomes to remove. Eventually, throwing everything away and starting over genuinely is the easier path — but it's a recommendation almost no manager wants to be the one to carry up to leadership.

This is exactly why this module exists where it does in the course: catching these mistakes during design, before code and data pile up on top of them, is dramatically cheaper than catching them later.

In the lessons that follow, we'll take a closer look at each of the four mistake areas above, one at a time, along with the specific mistakes most common to each. Before we get there, though, there's one more mistake worth covering here — one that touches every one of those four areas without belonging exclusively to any of them.

In a real sense, naming standards are a form of documentation baked directly into your schema. Done well, an object's name should tell you a great deal about it without any further explanation. Say I ask you to build an Employees table. You already know a lot about what belongs in it, without me spelling it out: name, address, phone number, email, and (in the United States, at least) a Social Security number. At most companies, you'd also expect an employee ID, hire date, job title, department, salary, and payroll details — deductions, a bank account for direct deposit, and so on. You'd probably also expect some link to a manager, and possibly to projects. All of that came from a single, well-chosen word: "Employees."

Now suppose instead I ask you to build a People table, but I actually intend to store employee data in it. You'd likely capture roughly half of what's actually needed. You'd get the name and address fields right without any trouble — but you'd almost certainly miss the business-specific fields entirely, because nothing about the name "People" suggested they belonged.

The problem compounds once you're working across multiple related tables. Say employee IDs link several tables together — but one table calls that column EmpNo, another calls it EmployeeId, and a third calls it Purchaser. In isolation, each of these is a minor inconvenience. Together, across a real schema with dozens of tables, small inconveniences like this accumulate into a genuine headache. Inconsistent naming forces developers to spend mental effort figuring out what a name means instead of thinking about what it represents — and that makes everyone working with the schema slower and more error-prone, indefinitely, for as long as the inconsistency exists.

Poor naming conventions have a way of turning what should be small changes into multi-day efforts, simply because developers have to keep jumping back and forth through the code trying to figure out what's actually happening. Inconsistent naming, by itself, is unlikely to sink an entire project — but it's more than enough to nudge an already struggling project further toward trouble.

The fix is straightforward in principle, if not always in practice: write down the names you'll use for fields that appear in more than one table, and stick to them, so the same concept gets the same name everywhere it appears. Consistency matters far more than which particular naming formula you follow — but two specific conventions are worth adopting regardless.

First, avoid using reserved keywords — words like TABLE, DROP, and INDEX — as table or field names. Even where your database technically allows it, using a keyword as an identifier makes code harder to read and easier to misinterpret. If a keyword genuinely fits your data well, make it more specific instead of using it bare: a seating-assignment database that really wants a field called Table is better served by TableNumber or AssignedTable.

Second, avoid special characters — spaces, in particular — in table or field names, even where the database technically permits them. There are ways to work around names like this, but they make every interaction with the database more awkward than it needs to be. The entire point of a good naming convention is to reduce confusion; a name that requires special handling just to reference works against that goal.

Choosing a good table name is a bit like a vocabulary test: you're looking for a word or short phrase that captures as much of what the table actually represents as possible, so that anyone else who encounters that name immediately understands what it's meant to hold. The figure below shows a few concrete examples of this in practice — vague or misleading names alongside more descriptive alternatives that make the table's purpose clear at a glance.

Naming might seem like a small, almost cosmetic concern next to constraints, relationships, and referential integrity — but it's the thread that runs through all four of the mistake areas ahead. A business rule you can't name clearly is a business rule you'll eventually misapply. A key column with three different names across three tables is a relationship waiting to break silently. Keep naming in mind as you move through the rest of this module; it rarely causes a mistake on its own, but it makes almost every other mistake in this module easier to make, and harder to catch.

The next lesson takes a closer look at mistakes associated with business objects and business rules — the first of the four areas above.