The architecture board meets for the fourth time about the new order management system. The whiteboard has two columns: the case for one framework and the case for the other. There is a performance benchmark, a comparison of GitHub stars, an analysis of the hiring market and a debate about which ecosystem will still be alive in five years. The meeting runs for two hours. The data model appears once in the minutes, under “to be settled by the team in sprint one”.

Three years later the same system gets a new requirement: a business customer can have several branches, each with its own delivery address and its own credit limit. In the code, that is a few days of work. In the data, it is weeks, because “customer” was one row in one table from the start, with the address, VAT number and credit limit in the same columns, and invoices, sales reports, the accounting integration and the warehouse export all sit on that table. Nobody remembers which framework was chosen or why. Everybody knows what it costs to change the definition of a customer.

This article is for architects, heads of data and the people who decide where a team spends its time at the start of a project. It defends one position that many experienced engineers will dispute: in a transactional application, a bad data model costs more than a bad framework, so design time should go first to the model, and framework debates should be kept short. This is not about the data warehouse or the analytics platform. It is about the model of the system that takes orders, issues invoices and serves customers.

Why does the framework debate take weeks while the data model happens in passing?

Because the framework is visible and the data model is not. A framework has a name, a logo, a community, conferences and popularity rankings. You can compare frameworks in a table and present the comparison to management. A data model is a few dozen tables and relationships that look obvious at first glance: customer, order, order line, product. It is hard to debate because everyone in the room has a different “obvious” customer in their head and does not know that the others have a different one.

Fred Brooks put it in The Mythical Man-Month in 1975, in lines still worth repeating: “Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious.” Half a century later, flowcharts have been replaced by frameworks, layers and patterns, but the proportion has not changed. The structure of the data tells you more about a system than the structure of the code, because the code is a way of handling the data, not the other way round.

There is a second reason. The framework decision has an owner and a deadline: the architect recommends, the board approves, done. The data model has no single moment of decision. It grows incrementally, in pull requests, often written by different people under deadline pressure. Each individual decision is small and reasonable in its context, and their sum becomes the most durable part of the system, one that nobody consciously designed.

The third reason is the simplest: framework costs show up immediately, model costs show up years later. A bad framework slows the team in the first months, so there is something to talk about. A bad model works fine until the first requirement it did not anticipate, and by then the decisions were made long ago and are hard to attribute to anyone.

What do we actually mean by the data model of a transactional application?

A data model is a set of decisions about what the system remembers and how. It covers entities (the things the system distinguishes and tracks: customer, contract, order), identity (how the system knows this is the same customer), relationships (an order belongs to a customer, a line to an order), rules that must always hold (an order cannot have a negative value, an invoice must have a buyer) and the way changes are recorded over time. The classic split into conceptual, logical and physical levels is useful, but in practice the cost of change is set by decisions that cut across all three. For the relational foundations underneath most of these systems, see the glossary entry on relational SQL databases.

A transactional application is a system that captures data at the moment something happens in the business: a customer places an order, the warehouse ships goods, finance issues a credit note. Its model must guard correctness at write time, because it is the source everything else draws on. That is what separates it from a warehouse model, which organises data for analysis and receives it already written. What an outside consultant does on a warehouse and how that work is priced is covered in our article on data warehouse consulting, and the choice between a central warehouse and a distributed approach in data mesh vs data warehouse. Here we care about the earlier link in the chain: the model whose mistakes the warehouse will inherit but never fix.

It also helps to separate the data model from the database technology. Choosing between a relational and a document database is a physical decision. The questions about identity, history and the boundaries of concepts have to be asked regardless of whether the data lands in tables, documents or an event stream. A document database does not exempt you from modelling. It just moves the modelling from the schema definition into application code, where it is less visible and harder to check.

Why can a framework be replaced piece by piece while a data model cannot?

A framework lives inside one application. Its reach ends at the process boundary: other systems do not know or care which framework handles orders, because they talk to the application through an interface or through the data. That is why a framework can be replaced module by module. The strangler fig pattern, which we describe in our article on replacing a legacy system piece by piece, works exactly this way: the new implementation takes over one function after another and the old one gradually disappears. Painful, but feasible and predictable.

A data model has a completely different reach. It is read not only by the application but also by reports, analysts’ queries, the accounting and logistics integrations, warehouse exports, admin scripts and, increasingly, machine learning models. Each of these consumers has encoded the model’s assumptions: that a customer has one address, that the “completed” status means paid, that an amount is in euros. Changing the model means finding and changing all of them, and some are outside the control of the team planning the change.

The second difference is historical data. Replacing a framework does not change a single stored record. Changing a model means transforming data written over years, often inconsistent, with exceptions someone once patched by hand. The transformation has to happen without stopping the business, so for a while both structures run at once. In 2017 Stripe described how it moved subscriptions, previously stored as an array inside the customer record, into a separate table. It covered hundreds of millions of objects and took four stages: writing to both places and backfilling old data, moving reads, moving writes and only then removing the old structure. All of it traced back to one early modelling decision: is a subscription part of a customer or an entity of its own?

The third difference: frameworks age predictably. The maintainer announces end of support, the community moves on, the team has years to react. Data models age unpredictably, because they depend on the business. A new sales channel, an acquisition, a regulatory change or a new pricing scheme can invalidate overnight an assumption that had been true from day one. You cannot put that date in the calendar.

When does a framework actually cost more than the model?

The thesis has limits, and they are worth naming before anyone concludes that the framework does not matter. A framework costs more when it stops being supported and the system has to meet security requirements. AngularJS is a good example: official support ended in January 2022, and companies with large applications built on it had to plan a migration, because nobody was patching vulnerabilities any more. With a well-designed data model, though, that was a migration of the presentation layer, while the data and business rules stayed where they were.

A framework also costs more when it pulls the data model inside itself. Object-relational mappers that generate the schema from code annotations, and frameworks with their own opaque storage formats, turn a framework replacement into a model change. That is not a counterargument to the thesis but a confirmation of it: what is expensive is whatever touches the data. That is why good practice keeps the schema as an explicit, versioned artefact rather than a side effect of classes in the code.

Finally, a framework costs more when the team cannot work in it. If you pick a technology you cannot hire for, every change will be expensive regardless of model quality. That dimension is real, and we cover it in our comparison of React, Angular and Vue for enterprise use. But notice that it is a fixed cost you can estimate in advance, not a hidden cost that surfaces with the first unexpected requirement.

The practical conclusion: choose a mature framework your team knows, and end the discussion. The difference between two mature frameworks rarely exceeds the cost of one badly modelled entity in a system that runs for five years.

How does the entity identity decision set costs for years?

Identity is the answer to the question: how does the system know that two records refer to the same thing? It sounds academic until you need to merge two accounts belonging to the same customer, split an account that wrongly combined two companies, or handle a customer that changed its name, legal form or registration number.

The most common mistake is using a business identifier as the primary key. VAT numbers, company registration numbers, national ID or social security numbers, product SKUs and email addresses all look like natural keys because they are unique and well known. The trouble is that they are unique in the business world, not in your system. A VAT number can be mistyped and corrected. A company can be restructured or acquired. A private customer can become a sole trader, and a sole trader can run several businesses. An email address changes when someone changes jobs. If the business identifier is the key that orders, invoices and contact history hang from, every such change becomes an operation across all related tables and every system that copied the key.

The safer pattern is a technical key with no business meaning, with business identifiers kept as attributes that have their own uniqueness rules and their own history. Correcting a VAT number then changes one attribute, not the identity. It is a decision that costs minutes at the start and weeks when reversed years later.

The second trap is gluing different things into one entity. A “customer” in a B2B system is usually at least three things: the legal entity that pays and receives the invoice, the location where goods are delivered and the person who places the order. When the model has a single customer table, the first requirement along the lines of “several branches under one payer” forces a rebuild. Patrick McKenzie’s widely cited 2010 post on falsehoods programmers believe about names listed assumptions such as “people have exactly one canonical full name” and “people’s names do not change”. Each of those assumptions, once written into a model, is an identity decision taken without realising it was a decision at all.

The third issue is duplicates. Any system that accepts data from people will end up with two records for the same customer. The question is not whether they will appear but what the model lets you do about them. Can you merge records and keep the history of both? Can you mark one as the survivor without deleting the other? Do invoices issued against the merged record still point to the correct buyer? This is where the application model meets master data management. Which data quality and MDM decisions are worth keeping in-house and which can be outsourced is covered in our article on data management services. From the model’s point of view one thing is enough: plan for merging from day one, because adding it later means reviewing every relationship in the system.

Why are time and history the most expensive things to add later?

Most systems start with a model that remembers only the current state. A price change overwrites the old price, an address change overwrites the old address, a change in contract terms overwrites the old terms. That works until the first question about the past: what was the price on the day the order was placed, which address did we ship to in March, which terms applied when the customer filed the complaint? If the model does not store that information, it cannot be reconstructed. You can start recording it today, but the past is gone.

The first line of defence is to store in the transaction whatever applied at the time of the transaction. An order line should hold the price, tax rate and discount at the time of ordering, not a reference to the product’s current price. An invoice should hold the buyer’s details as of the issue date, not a pointer to the current customer record. This is simple and cheap, yet it gets skipped, because during the first release prices had not changed yet.

The second line is effective dating. Price lists, contract terms, the sales rep assigned to an account or a credit limit all have a period during which they apply. A model that records “valid from, valid to” can answer questions about any given day and lets you schedule a change in advance. A model that holds only the current value forces someone to switch things over by hand at midnight and makes it impossible to reconstruct a past state.

The third line is bitemporal history. In a 2021 article, Martin Fowler illustrated it with a salary: a raise takes effect on 15 February, but HR learns about it later and then corrects the amount. You have to distinguish two timelines: when something was true in the world (actual history) and when the system learned about it (record history). Without the second timeline, you cannot explain why the February payslip was what it was, because the data has been corrected since. For billing, insurance, payroll and anything else subject to audit, this is not an academic nicety but the condition for answering an auditor’s question.

Databases help, but they do not make the decision for you. SQL Server has supported system-versioned temporal tables since the 2016 release: previous versions of rows move automatically to a history table and you can query the state “as of” a point in time. MariaDB has a similar mechanism. That only solves record history, though. The business effective date still has to be modelled, because the database has no idea when the customer’s new address took effect. Another route is to store events instead of states, known as event sourcing. It gives you full history by design, but it has its own costs: harder queries, event versioning and trouble with deleting data, which we come back to under GDPR.

Why is this the most expensive thing to add later? Because history cannot be retrofitted. Every other model defect can be fixed with a migration. Missing history can only be fixed from the day of the fix onwards.

How should you model money, currencies and units so you never rewrite billing?

Money in a data model comes down to three decisions: the number type, the currency and the rounding rules. Each looks like an implementation detail, and each can force a rewrite of the billing module.

The number type is the best known, yet it keeps coming back. Binary floating-point numbers do not represent decimal values exactly. The Python documentation shows it with a simple example: comparing 0.1 + 0.1 + 0.1 with 0.3 returns false, because 0.1 is not exactly one tenth. For amounts, that means one-cent discrepancies between the sum of the lines and the document total, which accumulate in reports and reconciliations. There are two remedies: a fixed-precision decimal type in both the database and the code, or integers in the currency’s smallest unit. Stripe’s API takes amounts as integers in minor units, so 1099 means 10.99 in any two-decimal currency.

The same example shows how long modelling decisions live. Stripe’s documentation describes the Icelandic króna and the Ugandan shilling as currencies that became zero-decimal, yet for backwards compatibility they still have to be sent as two-decimal values that always end in 00. A published rule for representing amounts outlived the reality it described, because changing the model on the API clients’ side would have cost more than keeping the exception.

The second decision is currency. An “amount” column without a “currency” column is an assumption that the system will always run in a single currency. That assumption looks safe until the company starts selling abroad, buying in another currency or settling contracts indexed to an exchange rate. Croatia is a clean illustration: it adopted the euro on 1 January 2023 at a fixed rate of 7.53450 kuna per euro, with both currencies accepted as payment for the first two weeks and prices displayed in both from September 2022 until the end of 2023. Every system that had treated “amount” as implicitly kuna needed a currency attribute, a conversion rule and a way to report periods that spanned the switch. Adding a currency to every amount in a system that has hundreds of them means reviewing every place amounts are summed, compared or reported, because adding kuna to euros is meaningless. At the start it is one column next to each amount and one rule: amounts in different currencies are never summed without an explicit conversion with a recorded rate and date.

The third decision is rounding and units. Is a percentage discount applied per line or per document? Is tax rounded per line or on the total? Are goods sold in pieces, cases and pallets, and if so, which unit is the base? These rules belong in the model, not scattered through the code, or every report and integration will calculate them slightly differently. Wherever structured e-invoicing becomes mandatory, systems that keep invoice data in a clean, explicit structure can map it to a structured format. Systems where part of that data lives in free-text fields and rules spread across the code have to reconstruct it first.

Why are statuses, flags and JSON fields debt that code review does not catch?

The “status” column is the most common place where a data model stops telling the truth. It starts with three values: new, in progress, completed. A year later it has twelve, including “partially_completed”, “cancelled_after_shipping” and “needs_review”, and nobody can say which transitions between them are allowed. Reports count “completed” three different ways, because each analyst interprets the later additions differently. The problem is not the number of statuses but the fact that the transition rules live in the code rather than the model. When the status is an explicit state machine, with a table of allowed transitions and a log of transitions with timestamp and author, a new status is a visible decision rather than a quiet extension of a list.

Boolean flags have the same problem on a smaller scale. A record with “is_active”, “is_blocked”, “is_deleted” and “is_archived” columns has sixteen possible combinations, of which perhaps five make sense. The other eleven will show up in the data sooner or later, because no rule forbids them. Every consumer then has to decide for itself whether a record that is both active and deleted counts as active or deleted. A single state column with a defined set of values is cheaper to maintain than four independent flags.

JSON fields in a relational database are a category of their own. They are useful for data whose structure genuinely varies and which the system does not interpret: the raw response from an external API, plugin configuration, product attributes that depend on category. They become debt when data the system makes decisions on goes in there simply because adding a column would have required a migration. A JSON field has no constraints, types or foreign keys, so the database does not guard its correctness. Every change to the structure inside the JSON is an implicit migration nobody ran, and old records keep the old structure forever.

What these three patterns share is that they look harmless in code review. Adding a value to the status list is one line. Adding a flag is one column. Dropping a new field into the JSON is zero schema changes. The cost lands where the reviewer cannot see it: in the reports, the integrations and with the analyst who, a year from now, will be trying to work out what the data means. That is why schema changes deserve their own review, with the question not “does the code work?” but “does the model tell the truth about the business?”.

Where does one model end and another begin?

The most expensive mistake at the level of the whole system is trying to build a single model for the entire company. It seems logical: one customer, one product, one truth. In practice, sales, finance and customer service understand “customer” differently. For sales it is an opportunity and a relationship, with a history of conversations and a potential. For finance it is a payer with a tax ID, a bank account and a balance. For support it is the person reporting a problem, often unrelated to who pays. A single table that tries to serve all three meanings has columns used by one department and ignored by the others, and every change needs everyone’s agreement.

In Domain-Driven Design, Eric Evans named this boundary the bounded context. In a short 2014 write-up, Martin Fowler quotes his observation that total unification of the domain model for a large system will not be feasible or cost-effective, and points to “customer” and “product” as typical words that mean different things in different parts of an organisation. The answer is several models, each consistent within its own area, with explicit mappings between them. The sales model has its customer, the billing model has its own, and they are linked by an identifier and a data exchange contract.

Model boundaries translate directly into the cost of change. If several applications read and write the same table, every change to its structure requires coordinating all the teams involved, and the team that wants to change something waits for the slowest one. If each area owns its data and other areas get it through an interface or events, changing the internal structure is one team’s business, as long as the external contract is preserved. This is the main reason integration through a shared database is so expensive to maintain: it turns one system’s internal model into a public interface for everyone.

A special case of a boundary is the partitioning of data between customers in a SaaS product. Deciding what a tenant is, and whether every record carries a tenant reference, is a modelling decision from the same family as identity, and adding it later touches every table. We break it down in detail in our article on the architecture decisions that set the cost of a SaaS product.

What does a schema migration in a live system really cost?

On a developer’s laptop, a schema change takes a second. On a production table with hundreds of millions of rows and users writing to it constantly, it is an operation that can stop the system. It pays to know how your database behaves before you plan a model change.

The PostgreSQL documentation states plainly that, unless noted otherwise, ALTER TABLE acquires an ACCESS EXCLUSIVE lock, which blocks reads as well for the duration of the operation. Some operations are fast: adding a column with a non-volatile default does not rewrite the table, because the value is stored in the metadata. Others are expensive: adding a column with a volatile default or changing a column’s type rewrites the entire table and its indexes, and the documentation warns this can take a long time and temporarily need as much as double the disk space. Adding a NOT NULL constraint requires a scan of the whole table. Fortunately, foreign keys and CHECK constraints have a NOT VALID variant: the constraint applies to new writes immediately, and validating existing rows is a separate step that takes a lighter lock.

In MySQL the problem was serious enough to produce dedicated online migration tools. In 2016 GitHub released gh-ost, explaining that it ran schema changes several times a day and that the existing trigger-based tools caused overload and lock contention at its scale. gh-ost reads changes from the database’s binary log and copies data into a ghost table, with throttling and the ability to pause a migration mid-flight.

Tools only solve the physical part, though. The logical part is solved by the expand and contract pattern. Danilo Sato described it in 2014 as parallel change: first you expand the interface to support both the old and the new version, then you migrate every consumer to the new one, and finally you remove the old one. In their article on evolutionary database design, rewritten in 2016, Pramod Sadalage and Martin Fowler add the discipline around it: every database change, including data fixes, is a numbered migration script in version control, applied automatically, and destructive changes go through a transition phase in which the database supports both structures, for example through a view under the old name.

What does this mean for cost? A model change in a live system is not one task but a series of steps spread over weeks or months, with a period in which you maintain two structures, synchronisation between them and result comparisons. The consumer migration phase is often the longest, especially when the consumers sit outside the team. That is the true price of a bad modelling decision: not an hour to write a script, but a quarter of work for several people with risk to production data. So any model that systems outside the team will read should be exposed through a contract (an API, a view, an event schema with compatibility rules) rather than direct table access. Confluent’s Schema Registry, for example, defaults to backward compatibility, which allows adding optional fields but blocks removing a required one. With a contract in place, the internal structure can change while the external contract evolves by explicit rules.

How do GDPR and data retention shape the model?

Data protection law is a modelling requirement, not only a legal one. Article 17 of the GDPR gives data subjects the right to obtain from the controller the erasure of their personal data, for example when it is no longer necessary for the purposes for which it was collected, or when consent is withdrawn and there is no other legal basis for processing. That sounds like a DELETE statement. In practice it collides with everything discussed above.

History and erasure are in tension. A model that never overwrites anything, because it keeps every version of a record or every event, holds copies of personal data in many places. The history table, the event log, the copies in the warehouse and the exports to partner systems all have to be covered by deletion or anonymisation. At the same time, some data must be kept because tax or accounting rules require it, so you cannot simply delete a customer together with their invoices.

The solution lies in the model, not the procedure. Personal data should be separated from transactional data, so that an order points to a person by an identifier, while the name, address and phone number live in one place that can be deleted or anonymised. Event-based models use a technique in which personal data inside events is encrypted with a separate key per person, and deleting the key makes the data unreadable without rewriting the immutable log. On top of that, the model needs an explicit retention policy: for each category of data, it is known how long it is kept and what happens to it afterwards.

These decisions cost little at the start and a great deal later. A system where personal data is scattered across dozens of tables, free-text fields and JSON columns needs a full inventory at the first serious erasure request. A system that separated personal data from day one and knows its retention rules handles such a request with a single, tested procedure.

How do you assess a data model before it becomes expensive?

The cheapest moment to fix a model is the week before the first production release. The second cheapest is now. The table below lists decisions worth reviewing at any stage of a system’s life, with a check question and an estimate of the cost of changing it later. The cost estimate is a qualitative judgement based on how many places in the system the decision touches, not a measured value.

Modelling decisionCheck questionTypical mistakeCost of change years laterCheap decision at the start
Entity identityWhat happens when a customer changes VAT number, name or email?Business identifier as primary keyHigh: every relationship and every system that copied the keyTechnical key, business identifiers as attributes with history
Entity boundariesAre the payer, the delivery location and the ordering person one thing?One “customer” table for everythingHigh: rebuilding relationships, reports and integrationsSeparate entities with explicit relationships
Duplicate mergingCan two records be merged without losing history?No merge, manual deletionMedium to high: review of every relationshipSurviving record marked, merged records kept
History and timeWhat were the price, address and terms on day X?Overwriting the current stateCannot be fixed retroactivelyValues captured at transaction time, effective dates, history table
MoneyIn what currency and with what precision is this amount?Floating-point type, no currencyHigh: every sum and reportDecimal type or integers, currency on every amount
StatusesWhich transitions are allowed and who performed them?List of values with no transition rulesMedium: reinterpreting historyState machine with a transitions table and log
Flexible fieldsDoes the system make decisions on data inside JSON?Business data in a schemaless fieldMedium: implicit structure versions in old recordsJSON only for uninterpreted data
External consumersWho outside the application reads these tables?Direct table accessHigh: coordinating every consumerContract: API, view, event schema
Personal dataHow do you erase one person’s data everywhere?Personal data spread across tables and historyHigh: inventory and history rewritePersonal data separated, retention policy
MigrationsIs every schema change a script in version control?Manual changes in productionMedium: environment drift, risk on every changeNumbered migrations, expand and contract

How do you use this table? Take the three most important entities in your system, usually the customer, the main transactional document and the product or service, and go through each row with someone who knows the business and someone who knows the database. Every “I don’t know” or “it depends” is a candidate for a decision worth making deliberately. You do not have to fix everything at once. You only need to know which decisions are cheap now and expensive in a year, and start with those.

For a system that is already live, a sensible first step is an independent review. The team that built the model sees it from the inside and often misses the assumptions it made at the beginning. An outside view of the architecture and technical debt helps establish which modelling decisions are the most expensive and in what order to change them. At ARDURA, the Code Health Audit has a fixed scope, takes one to two weeks and starts from €4,500.

What are the key takeaways for architects and heads of data?

A framework is a decision about how the team writes code. A data model is a decision about what the company will know about itself for years to come. A framework can be replaced module by module, and its end of life is visible well in advance. A data model is shared by every consumer of the data, and data written in a bad structure has to be transformed without stopping the business. That is why design time should go first to the model.

Remember six things. First, separate technical identity from business identifiers and plan for duplicate merging from the start. Second, store values as of the transaction in every transaction and model effective dates wherever the business asks about the past, because history cannot be added retroactively. Third, store amounts in an exact type, always with a currency. Fourth, turn statuses and flags into explicit state machines, and keep JSON fields for data the system does not make decisions on. Fifth, draw boundaries between models and expose data to others through a contract, not shared tables. Sixth, plan schema changes as a series of expand-and-contract steps, and separate personal data before the first erasure request arrives.

If you are designing a new system or want to assess the data model of one that is already running, see how we work in custom software development, or book a call. Discovery takes one to two weeks and ends with a defined scope and architecture, including the data model decisions that will set the cost of change for years to come.