Database schema decisions that hurt in year two

Frameworks get upgraded, front ends get redesigned, and teams turn over, but the database schema tends to stay. Data written in the first month of a product is still there five years later, shaped by decisions someone made under deadline pressure in week two. When we audit applications in their second or third year, the schema is where many of the most expensive problems live, because fixing it means migrating data that the business depends on while the application keeps running. None of the mistakes below are exotic. They are just cheap to avoid early and costly to fix later.

Money stored as floating point

Storing prices and balances as floating-point numbers is the most common issue we find, and it produces bugs that are hard to spot: totals that are off by a cent, refunds that do not quite match charges, reports that disagree with the payment provider. Floating-point types cannot represent most decimal fractions exactly.

Store money either as an integer number of the smallest currency unit, such as cents, or as a fixed-precision decimal type, and always store the currency alongside it. A price without a currency is a bug waiting for your first international customer.

CREATE TABLE order_lines (
  id            BIGINT PRIMARY KEY,
  order_id      BIGINT NOT NULL REFERENCES orders(id),
  unit_amount   BIGINT NOT NULL CHECK (unit_amount >= 0),
  currency      CHAR(3) NOT NULL,
  quantity      INTEGER NOT NULL CHECK (quantity > 0)
);

Constraints left to the application

Many codebases rely entirely on application-level validation: the model checks that an email is unique, that an order belongs to a customer, that a quantity is positive. That works until a second code path writes to the table, such as an import script, an admin tool, a queue worker or a new service, and does not run the same checks. By year two, there are almost always orphaned rows, duplicate records and impossible values.

Put the rules that must always hold into the database:

• Foreign keys for every relationship, with deliberate choices about what happens on delete.
• Unique constraints for anything that must be unique, including composite ones such as one active subscription per account.
• NOT NULL on every column that should never be empty, which is most of them.
• Check constraints for simple invariants such as positive quantities or valid status values.

Application validation still matters for friendly error messages. Database constraints are what guarantee the data is correct no matter how it got there.

Validation in the application is a courtesy to users. Constraints in the database are a promise to your future self.

JSON columns as a substitute for design

Modern databases handle JSON well, and a JSON column is the right tool for genuinely unstructured data such as raw webhook payloads or per-integration settings. It becomes a problem when it replaces modeling. We regularly see core business attributes, such as product dimensions, customer tiers or order metadata, stored in a JSON blob because it was faster than writing a migration. A year later, reports need to filter and aggregate on those fields, the shapes have drifted across versions, and nothing enforces what is in there.

Our rule: if you will query it, filter on it, join on it or report on it, it gets a real column. JSON is for data you store and pass through, not data you reason about.

Time without time zones

Timestamps stored in server local time, or without any zone information, break the moment a product serves more than one region, changes hosting provider or crosses a daylight saving boundary. Store all timestamps in UTC using a timezone-aware type, convert to local time only for display, and store the user's time zone separately where business logic depends on it, such as sending a reminder at nine in the morning local time.

Dates without times, such as a birthday or a subscription renewal date, are a separate concept and should use a date type rather than a timestamp at midnight, which silently shifts by a day when converted between zones.

Deleting what you will later need

Hard deletes feel clean, but the business often needs history later: what a customer ordered before they closed their account, what a price was when an invoice was issued, who changed a setting and when. Three patterns help:

1. Snapshot values at the time of the event. An order line should store the price and product name as they were at purchase, not only a reference to the current product.
2. Soft delete where recovery matters, with care: every query must respect it, and unique constraints must account for it.
3. An audit table for sensitive changes, recording who changed what, from which value, and when.

Privacy regulations add a counterweight: personal data must be deletable on request. Design for both by separating personal fields that can be anonymized from transactional records that must be kept.

Identifiers and status fields

Two smaller decisions round out the list. Exposing sequential integer IDs in URLs leaks business volume and invites enumeration; use opaque public identifiers alongside internal keys. And status columns stored as free text accumulate variants such as "cancelled", "canceled" and "Cancelled"; use an enum or a lookup table, and document the allowed transitions between states.

Getting it right from the start

A few extra hours of schema design and review in the first sprint prevent weeks of migration work later. On every build, we review the initial schema as a team before the first migration is merged, and again at each major feature. For existing applications, our database architecture reviews identify these issues and plan safe, incremental fixes, and when growth is the real problem, our database scaling work picks up from there. The same standards apply across our custom web application builds.

Have us review your schema

If your data is starting to fight back, or you are about to design a schema that needs to last, we can help. Send us a short description or an export of your schema and we will quote a fixed-price review within 24 hours. Contact our team.