Database Column Mistakes

Designing tables for a relational database really comes down to three tasks: create columns to hold individual pieces of data, create constraints that define what's actually permissible in those columns, and define the primary and foreign keys that tie tables together. That last piece — constraints and keys — gets its own full treatment in the next lesson. This lesson stays focused on the first task: columns themselves, and the three mistakes that show up most often once you actually start naming and structuring them.

1. Representing more than one attribute in a column
2. Duplicating column names across different tables
3. Giving columns cryptic names

There's a fourth mistake worth covering here too, even though it's not strictly about naming: underestimating how much load your columns and tables will actually need to handle once real users show up.

Just as you should resist the temptation to store more than one business object in a single table, you should resist squeezing more than one attribute into a single column just to make a table look smaller or simpler. It's tempting, for example, to store a distributor's street address, city, state, and ZIP code as one combined text field. Don't. Each of those is genuinely a separate attribute, and collapsing them together costs you real capability later — it's far easier to answer a question like "which states do we sell the most CDs to?" when state lives in its own column, rather than being buried somewhere inside an eighty-character text string you'd have to parse to extract it.

A less subtle mistake, but a confusing one, is giving columns in different tables the exact same name. Take the Distributor table from the course project, with fields like DistStreet, DistCity, and DistState. The Customer table from earlier in this course shares several of the same conceptual values — street addresses, cities, states. If both tables simply used generic column names like Street, City, and State, you'd have no way to tell at a glance which table a given column actually came from without checking the data dictionary every time. Prefixing consistently — CustStreet, CustCity, CustState for the Customer table, versus DistStreet, DistCity, DistState for the Distributor table — makes a column's origin obvious from the name alone.

Note: the exception to this guideline is a foreign key column. A foreign key should always keep the exact name it has in the table where it's the primary key, rather than being renamed or prefixed to match the table it's now sitting in. Consistency across the relationship matters more than consistency with its new table's naming pattern.

In the interest of saving a bit of typing (and your wrists), it's tempting to assign short, cryptic names to columns. Resist that too. Cryptic names cause real trouble down the line, once it's time to actually start entering data or writing queries against the table — anyone working with the table has to stop and figure out what a given column is actually meant to hold, every single time, rather than being able to tell at a glance. And the original motivation for short names — saving disk space — simply isn't the constraint it once was; modern storage is cheap enough that a few extra characters per column name cost nothing that matters.

The general rule: give columns descriptive names that briefly convey both the table and the attribute — DistStreet for the distributor's street address, CustStreet for the customer's. A column name that takes an extra half-second to type but saves everyone who touches the table from guessing is a trade worth making every time.

It's a familiar story: a perfectly reasonable database design gets built, and only in the final stages of the project does anyone discover it can't actually handle the load being thrown at it. The fix isn't complicated in principle — estimate your database's storage and transaction load, estimate the network traffic that comes with it, and then multiply your estimate by five as a safety margin. For applications with genuinely unpredictable spikes — a public web application that might see enormous traffic surges over just a few hours — multiplying by ten or more isn't unreasonable.

The part that's easy to get wrong isn't the multiplier — it's the baseline you're multiplying. Base your estimates on realistic assumptions about the hardware and networks your actual users will have, not the hardware you're developing on. It's common practice to give developers powerful, well-resourced machines so they can work efficiently. That's a reasonable investment in a handful of developers. It is not a reasonable stand-in for what your actual user base will be running: a modest, one-time hardware investment across a small development team is a completely different scale of cost than equipping every one of your end users with comparable machines, especially once that user count reaches into the hundreds. Build your capacity estimates around what your customers can actually afford to deploy, not around your own development environment.

If your architecture genuinely can't handle the load you're projecting, that's worth confronting before launch, not after. There are several directions to take: a more powerful server, more disk, a faster network, or splitting data across multiple servers. If those don't get you far enough, consider a three-tier architecture with middle-tier logic distributed across separate machines, or moving computationally intensive work out of the database layer entirely and into code running elsewhere. In some cases, it's worth restructuring the database to store precomputed, ready-to-use results for common queries, rather than recalculating them from scratch on every request. In the most demanding cases, splitting the database itself into independent pieces that can run on separate servers may be necessary.

None of these fixes are easy, but the alternative is worse. The surest way to burn through customer goodwill is to build excitement, ship the database, and then have to tell everyone it's unusable for the next four months while you rework its performance from the ground up. Plan for load before you need to, not after it's already a crisis.

The next lesson examines mistakes associated with constraints and keys.