--- title: "Database Design: Decisions That Bite Back Two Years Later" description: "A badly designed database does not show up immediately. It shows up when it holds half a million records and every change means rewriting half the application." canonical: "https://interfase.sk/en/news/database-design-decisions-that-bite-back-two-years-later" lang: "en" last_updated: "2026-11-04" --- # Database Design: Decisions That Bite Back Two Years Later > A badly designed database does not show up immediately. It shows up when it holds half a million records and every change means rewriting half the application. Published: 2026-11-04 - Design mistakes do not surface at a hundred records but at hundreds of thousands — exactly when fixing them costs the most. - Records that mean something historically are never deleted, only marked inactive. - One field carries one piece of information; combined fields cannot be filtered or corrected later. - The price on an order is stored with the order — a reference to the live price list means old documents change by themselves. The first year is fine. The application runs, data accumulates, nobody complains. Then a request arrives that sounds trivial — "we want to see how the order changed" — and the answer is that it cannot be done, because that information was never recorded anywhere. This is the typical story of database design. Its mistakes do not hurt straight away; they hurt exactly when fixing them is most expensive. ## Do not delete, mark The most common and most expensive mistake. A deleted customer takes their order history with them. A deleted product turns invoices into nonsense. A deleted user makes it impossible to answer who made a change. The rule: **a record something else refers to is never deleted**. It is marked inactive and stops being offered in new operations. Real deletion happens only where the law requires it — and then deliberately, knowing what will happen to the references. This connects to [access audits and security logs](/blog/audit-pristupov-a-bezpecnostne-logy-vo-firemnej-aplikacii): without a record, nothing can be proven. ## Current state is not history The second classic mistake is storing only how things look now. A `status` column reading "fulfilled" says nothing about when that happened or which stages the order passed through. If there is even a suspicion that the sequence will be analysed one day — and there almost always is — the record needs **a change history** as a table of its own. It cannot be backfilled, because the data does not exist. ## The price belongs to the document, not the price list An order that pulls its price from the live price list rewrites itself every time prices change. Last year's invoices suddenly show different amounts from the ones the customer paid. The same goes for addresses, VAT rates, product names and terms. **A document is a snapshot of the state at the moment it was created** and keeps its own copy of everything material. ## One field, one piece of information A `notes` field mixing contacts, promises and internal comments cannot be searched or cleaned a year later. The same applies to combinations like "first and last name in one column" or "address as a single block of text". Splitting them is easy at the start and hard afterwards — at tens of thousands of records it is a job of weeks and never comes out without errors. ## Identifiers and formats - **Do not use anything that can change as a key** — not an email, a company number or an internal reference. People change emails, companies merge. - **Numeric codes with a leading zero are not numbers.** Postcodes and bank codes belong in text, or "01234" becomes "1234". - **Store time in UTC** and convert only on display. Otherwise the daylight-saving switch turns hourly timesheets into a puzzle. - **Do not store money as a floating-point number.** Rounding errors show up in the totals. ## Speed is a matter of design, not the server An application that is slow at a hundred thousand records is usually slow because of missing indexes and queries that load more than they need. A bigger server postpones that by a few months; it does not solve it. We went into this in [where time is really lost in a business application](/blog/rychlost-firemnej-aplikacie-kde-sa-realne-straca-cas). ## What to do when it is already too late If the application is live and the design is bad, rewriting the whole database at once is not the answer. What works is incremental: **add what is missing** (history, an inactive flag, absent fields), **stop deleting**, and build new parts properly. Splitting combined fields comes only once it is clear what is actually in them. ## Where to start With two questions: what does our database **delete**, and **where do we lack history** in places we would want it. The answers tend to be the first list of things worth fixing before more data arrives. If you are building a new application or dealing with an existing one, [get in touch](/kontakt). We handle it as part of [custom development](/riesenia/vyvoj) — examples in our [case studies](/referencie). --- Web version: https://interfase.sk/en/news/database-design-decisions-that-bite-back-two-years-later llms.txt: https://interfase.sk/llms.txt