Common Database Design Mistakes: Conclusion

This module started with a simple, deliberately practical question: not "what does good database theory look like," but "what actually goes wrong when that theory meets a real project, a real deadline, and real developers who'd rather start coding than keep planning?" Nine lessons later, the mistakes turn out to cluster into four recurring areas — business objects and rules, constraints/columns/keys, relationships and referential integrity, and international issues — with one thread running underneath all four, and one final lesson on how to tell, after all of it, whether the database you built is actually any good.

Before any of the four specific areas, this module identified two root causes that make every other mistake more likely. The first is lack of preparation: database design happens early in a project, and the temptation to skip straight to writing code — visible progress you can show management — is exactly what causes the worst mistakes downstream. Understanding the problem, writing requirements, building use cases, designing a solution, testing it, and documenting all of it isn't overhead; skipping any one step is what makes the rest of this module's mistakes far more likely to actually happen. The second is poor documentation specifically — not busywork, but the mechanism that keeps everyone on a project pointed at the same goals, so different people don't quietly make different, colliding assumptions about how the system works.

One more thread runs underneath all four mistake areas without belonging exclusively to any of them: naming. A well-chosen table name (Employees, not People) tells you what belongs in it before anyone explains a thing. Inconsistent naming across tables — the same concept called EmpNo in one table and Purchaser in another — doesn't sink a project by itself, but it makes every one of the mistakes below easier to make and harder to catch. Keep that in mind as the four areas unfold below; naming discipline is the cheapest insurance this module offers.

The Stories on CD example carried this area throughout the module: a real business, fully represented by just four business objects — CDs, Categories, Distributors, and Orders. Mistakes here run in two opposite directions. Cramming more than one business object into a single table — a distributor's address folded into the CD table — produces three specific, well-understood failure modes: update anomalies (miss one duplicated row and your data disagrees with itself), insertion anomalies (can't add a distributor without a CD to attach it to), and deletion anomalies (delete the one CD order and the distributor's information vanishes with it). The fix is straightforward normalization — separate tables, linked by a foreign key.

The opposite mistake is overstuffing: adding objects or attributes the business doesn't actually need, which costs real performance (wider tables mean larger data pages read from disk), leaves tables littered with NULLs, and can even create locking contention when unrelated attributes crammed into one row block each other's updates. And business rules deserve the same scrutiny as objects — a rule only belongs in the database if it genuinely preserves data integrity and won't force a schema revision the moment the business changes. Rules too fuzzy to check reliably belong in application logic or human judgment, not baked permanently into constraints.

This is the area the module spent the most time on, across three full lessons, and for good reason — it's where technical mistakes translate most directly into corrupted or unreliable data.

At the column level: don't cram multiple attributes into one field (a distributor's full address as one string instead of separate street/city/state/ZIP columns), don't duplicate column names across tables without a clarifying prefix, and don't give columns cryptic names to save a few characters — modern storage makes that tradeoff pointless. And don't think too small: estimate real load, multiply by a safety margin, and base every estimate on the hardware your actual users will have, not your own development machine.

At the constraint level: a mistyped CHECK constraint can silently enforce the wrong rule entirely — the module's own worked example showed how CHECK (OrderCost < 1 AND OrderCost < 500) quietly collapses to just OrderCost < 1, rejecting every valid order without ever announcing it's wrong. Relying solely on application-level validation leaves the schema defenseless against manual scripts or a second application connecting directly. And NOT NULL gets misused in both directions — underused, it produces sparse data and complex queries; overused without sensible defaults, it causes inserts to fail constantly.

At the key level: every table needs an explicit primary key, never assumed uniqueness. Natural, meaningful keys (email, phone, SKU) are fragile because they change, and that change cascades expensively through every referencing foreign key — surrogate keys (auto-increment integers, UUIDs) solve this by being immutable and business-meaning-free. Foreign keys need to actually be declared, not just implied by matching column names, or orphaned records accumulate silently. They also need explicit indexes — a foreign key relationship doesn't automatically create one, and Oracle Database specifically does not index foreign keys by default, which can turn a routine delete or join into a full table scan on a large table. Primary and foreign key columns need exact type, length, and collation parity, or the query optimizer is forced into implicit conversions that quietly defeat index usage. Composite keys belong on junction tables resolving many-to-many relationships, not on primary entity tables where they'd force every related table to carry multiple redundant columns. ON DELETE CASCADE is convenient but dangerous for anything with real historical value — audit logs, transaction history — where a soft delete or ON DELETE RESTRICT is the safer default. And normalization itself has a ceiling: technically reaching Fifth Normal Form while producing a schema nobody can query without a dozen joins is its own kind of failure.

Relationships are what make a relational database more than a collection of isolated tables — but not every possible relationship should actually be built. Creating a relationship between two tables that have nothing to do with each other (Category and Distributor, in the Stories on CD schema) just adds false structure. A one-to-many relationship only needs one foreign key, pointing one direction; there's no need to "complete the circuit" with a redundant reverse key. And referential integrity itself should be enforced deliberately, not reflexively — only when an actual business rule requires it, since enforcement costs real processing time on large tables.

Three structural anti-patterns strip out referential integrity entirely, and are worth remembering by name: the comma-separated many-to-many (a Genre_IDs column holding "1, 4, 12" instead of a real junction table), which breaks indexing and makes orphaned references undetectable; polymorphic associations (a generic Entity_ID plus Entity_Type string trying to point at multiple different parent tables), which can't support a valid foreign key constraint at all; and circular dependencies (Table A → B → C → A), which create a genuine chicken-and-egg insertion problem and usually signal a deeper design flaw, not just an inconvenience. On the other end of the spectrum, overusing one-to-one relationships — splitting off a table that shares its parent's exact primary key just to hold a few extra columns — forces an unnecessary join on nearly every query for no structural benefit.

The final specific mistake area is the one most schemas never test until it's expensive to fix: assumptions baked in around one culture, one language, one format. Storing timestamps in local time instead of UTC loses its absolute anchor the moment a user travels or daylight saving time shifts. Hardcoding first_name/last_name breaks for cultures using mononyms, patronymics, or reversed name orders. Rigid "State" fields and numeric ZIP codes reject countries that don't organize addresses that way, or use alphanumeric postal codes like the UK's SW1A 1AA. Character encoding and collation are separate technical problems — get encoding wrong and text corrupts into "mojibake"; get collation wrong and text sorts incorrectly without ever corrupting. And money stored as FLOAT without a paired ISO 4217 currency code loses precision and becomes ambiguous the moment a second currency enters the picture.

The fixes share a common shape: generic, flexible, nullable columns (full_name, region_or_province, postal_code as VARCHAR) with validation logic handled by the application rather than rigid database constraints — loosening the schema's assumptions costs very little up front and avoids exactly the kind of devastating late-stage refactor retrofitting internationalization requires.

All nine lessons before this one are really in service of a single closing question: is the database you end up with actually useful? The technology itself is never inherently wrong — a relational database, a document store, a graph database, each solves a specific problem well. What makes a database useless is misalignment: the wrong tool forced onto the wrong problem, garbage data poisoning an otherwise well-architected schema, or poor indexing making a technically correct database impossible to query in reasonable time.

That's the real point behind this module's guiding statement: "There are no wrong databases, just useless ones." A database that runs a bit slowly but reliably answers real questions beats a lightning-fast database containing no actionable information. And that same instinct, taken too far, becomes its own mistake — developers who denormalize and over-engineer in the name of performance often save milliseconds on a query that already runs in one second, while making the system far harder to build, debug, and maintain. First, make it work. Then make it work fast. And underneath all of it: a database that hasn't actually been tested against realistic load and realistic use cases hasn't really been evaluated at all — it's just been hoped about.

Every mistake in this module is avoidable, and every fix follows the same underlying discipline: understand the business objects and rules before you model them, get columns, constraints, and keys right at the schema level rather than trusting application code alone, build only the relationships you actually need and enforce integrity where it matters, design with international assumptions loosened from the start, and evaluate the result against real usefulness rather than theoretical correctness. In the next module, we move into more advanced query-writing techniques and explore the Oracle extensions to standard SQL that add real power to how you write them.

Before moving on to the next module, click the Quiz link below to reinforce your understanding of common design mistakes.
Database Table - Quiz