Designing the ERP Master Data Model Before You Migrate a Single Record

What the ERP Master Data Model Actually Is

An ERP master data model is the structural blueprint that defines how the four core master entities — the chart of accounts, the item or product master, the customer (and vendor) master, and the financial dimensions — are organized, coded, and related to each other. It is not the data itself; it is the schema, the numbering scheme, the segmentation rules, and the hierarchy that the data will live inside for the life of the system. Deloitte describes the chart of accounts component as a finance data model: the common data definitions and dimensions an organization uses to record, report, and measure performance, and which must align to the way the business wants to manage and report now and in the future.

What makes the master data model different from everything else in an ERP project is its permanence. Oracle's official Financials documentation is blunt about this: once you begin using your chart of accounts, making changes to its fundamental attributes is neither recommended nor supported, including the segments and their labels. The same is true of a poorly designed item master or a customer master that mixes identity with role. You can add accounts, items, and customers freely, but you cannot easily renumber the chart of accounts, restructure the item hierarchy, or change the cardinality of a financial dimension after go-live without a painful remediation project. The model is effectively poured into concrete at cutover.

This is why the design phase is distinct from the two activities that surround it. Data migration is the one-time move of records into the model once it is built; data governance is the ongoing discipline that keeps those records clean. Designing the model badly and then migrating into it perfectly, or governing it diligently, still produces a system that reports the wrong things, breaks at scale, and resists every future change. For an SME moving onto Dynamics 365 or Odoo, the model is designed once, in a workshop pairing the finance lead, operations lead, and implementation architect — and the decisions made there shape financial statements, valuation, and analytics for the next decade.

Five Principles That Govern Every Design Decision

Before touching any specific entity, agree on the principles that govern every decision across the model. The first and most important is to design backward from reporting requirements. Oracle's COA best practices are explicit that the design should start from external and management reporting requirements and then decide how data is stored in the general ledger. In practice this means listing the financial statements, management reports, segment-level P&Ls, and KPIs the business needs at go-live and over the next three years, and building a model that produces them without manual manipulation. If you cannot produce a required report from the model's structure, the structure is incomplete.

The second principle is to separate structural data from transactional data. Master data (the account, the item, the customer) changes rarely and defines the business; transactional data changes constantly and is classified by the master data. A common design failure is encoding transactional or situational information into the master record — a separate GL account for each project, a separate customer account for each order — which causes the master to balloon and breaks reporting. Project, cost-centre, and department slicing belong in financial dimensions, not the chart of accounts. The remaining principles are standardization (one definition and one format per concept), single source of truth (one authoritative definition per entity), and flexibility: build numbering ranges and hierarchies that absorb growth without renumbering. Oracle and Deloitte both warn that a model sized for today's account count will be rebuilt within five years.

A specific architectural decision that falls out of these principles is the thick-versus-thin general ledger. Oracle frames this as a deliberate choice in how much detail you store in the GL versus in sub-ledgers and dimensions. A thick GL encodes many segments directly in the account combination (company, division, department, account), which is rigid; a thin GL keeps the natural account segment lean and pushes management detail into financial dimensions or analytic plans, which is flexible. For most SMEs a thin GL with a disciplined dimension model is the more future-proof choice, because it lets you add reporting axes without touching the chart of accounts.

Chart of Accounts Design: Segments, Numbering, and Discipline

The chart of accounts is the spine of the master data model because every financial transaction ultimately resolves to an account combination. Oracle's Cloud ERP guidance defines the COA structure as the general outline of the chart of accounts, where each segment represents a legal, fiscal, or management dimension the enterprise uses to measure activity. In Dynamics 365 Finance & Operations the main account is always the first segment, followed by up to ten financial dimensions in the account structure; in Business Central and Odoo the main account number stands alone and management axes are handled through dimensions and analytic plans. Whatever the platform, the design questions are the same: how many segments, what each means, and how the account numbers are laid out.

A disciplined chart of accounts design uses block numbering for the natural account segment, with reserved ranges per account type so accounts can be added without renumbering. A common SME layout reserves ranges such as 1000-1999 for current assets, 2000-2999 for long-term assets and liabilities, 3000-3999 for equity, 4000-4999 for revenue, and 5000-9999 for expense classes, with gaps between blocks for future accounts. The account type (asset, liability, equity, revenue, expense) drives behaviour — it determines which financial statement an account rolls into, its normal balance, and its year-end treatment — so it must be assigned correctly at design time, because correcting a misclassified account after a year of postings is a significant reconciliation exercise.

The cardinal rule, repeated by Oracle, Deloitte, and Microsoft, is do not overcomplicate the chart of accounts. Oracle warns explicitly that over-complicating the COA makes it hard for finance staff to use, and recommends meaningful segment names with relevant values for accuracy and trust in financial statements. The most common over-complication is encoding management reporting detail — department, project, region — as additional account numbers instead of as financial dimensions. If you find yourself creating accounts like 6010-MKTG-NE and 6010-MKTG-W, you have designed a thick GL that will become unmaintainable; the correct design is a single account 6010 (Marketing expense) sliced by Department and Region dimensions. Segment order also matters: Oracle's most common order begins with company, then cost centre, then natural account, while Microsoft recommends putting the main account first so the system can apply correct default values and limit which dimension values are valid in combination.

Whatever order you choose, it should be documented, signed off by the CFO, and treated as fixed. Because — to repeat the single most important constraint on this entire guide — Oracle documents that once the chart of accounts is in use, changes to its segments and labels are neither recommended nor supported. The COA design is the one decision in the whole ERP project you cannot easily reverse.

Item Master Design: SKU Strategy, Hierarchy, and Variants

The item master (also called the product master) is the repository of information about everything the organization purchases, manufactures, stocks, and sells. A thorough design groups this information into logical clusters: basic identification (name, description, unique SKU), product hierarchy (brand, family, category), detailed specifications (dimensions, weight, materials, technical properties), commercial attributes (pricing, cost, product family), inventory parameters (reorder points, units of measure, master case and inner pack), supplier data (lead times, minimum order quantities), manufacturing data (bill of materials, routing), and compliance information (country of origin, certifications, hazardous-material class). Each cluster is a design decision: which are mandatory, which are optional, and which drive downstream behaviour like valuation, replenishment, and tax.

The single most consequential item-master decision is the SKU-versus-variant strategy, and it is where most SMEs get the model wrong. The question is whether each colour-and-size combination of a product is its own item (a separate SKU) or an attribute of a single parent item (a variant). Too few attributes and you lose the reporting precision operations needs; too many and the master balloons into tens of thousands of near-identical records nobody can maintain. Both Dynamics 365 and Odoo support a parent-item-with-variants model, and the design discipline is to choose your variant axes — typically two or three, such as colour, size, and configuration — and force every other difference into attributes rather than new SKUs. A rule of thumb: if two items share a bill of materials and differ only in a saleable attribute, they are variants of one item, not two items.

Units of measure are the second design trap. The item master must store a base unit of measure (the unit in which inventory is valued and counted) and a conversion table that translates every other unit — purchase unit, sales unit, master case, pallet — into the base unit. Design the conversion table once, at the model level, and let items reference it. Getting this wrong produces silent inventory errors: a case of 12 ordered against a base unit of pieces, with no conversion, either blocks the transaction or posts twelve times the expected quantity. Tax and posting groups — which determine how an item is taxed and which accounts it posts to — are also part of the design, because a misclassified posting group routes every movement of that item to the wrong GL account from day one.

The costing method is the final design decision that is effectively permanent: standard cost, FIFO, LIFO, average, or specific (serial/lot tracked). It is set per item and governs how inventory is valued, how cost of goods sold is recognized, and how variances are handled. Microsoft documents that changing the costing method after transactions exist is a disruptive correction requiring inventory revaluation, and the same applies in Odoo. Pick the method deliberately per item category during design — finished goods often standard cost, traded commodities often FIFO or average, high-value serialized items often specific identification — and document the rationale.

Beyond the parent-and-variant model sits the product configurator decision, and it is the difference between a maintainable catalogue and a Cartesian explosion. Microsoft documents product configuration models in Dynamics 365 Supply Chain as a generic product structure that produces distinct variants with unique BOMs and routes, driven by attributes, expression or table constraints, and calculations — not by pre-creating every combination as a stocked SKU. In Odoo, standard attributes create variants as a Cartesian product of attribute values: five attributes with five values each produces 3,125 variants, and community reports note measurable performance degradation past roughly 1,000 variants per template, with large imports of 8,000+ variants taking hours. The design rule is architectural, not cosmetic: use discrete variants when combinations are finite, stocked, and inventory-planned (colour × size apparel, fixed electronics SKUs); use a constraint-based configurator when options interact, dimensions are continuous, or BOMs and pricing are formula-driven (engineer-to-order equipment, custom furniture, variable packaging). Do not enable every attribute as Instantly-created variants hoping the catalogue will stay tidy — it will not.

Attribute modelling discipline keeps either path healthy. Cap saleable variant axes at two or three for stocked products and put technical or marketing properties (finish grade, warranty tier, marketing family) on attributes that do not explode inventory. Odoo's variant creation mode (Instantly, Dynamically, or Never) is set per attribute and cannot be edited once the attribute is used — choosing Instantly for a high-cardinality option locks you into combinatorial SKUs that require rebuild to reverse. Dynamics product masters separate product dimensions (configuration, size, colour, style) from free-form attributes and from constraint-based configuration technology; pick the configuration technology on the product master at design time. Document exclusion rules (invalid combinations) and survivor keys for duplicates before migration so the item load does not invent parallel SKUs for the same physical good.

The Customer Master and the Party Model

The customer master is where most SMEs accumulate their worst data quality, and most of that damage is a design problem rather than a hygiene problem. The root cause is conflating two different concepts in one record: the identity of who the customer is (the legal entity, its name, its addresses, its contacts) and the role that entity plays in a specific business relationship (credit limit, payment terms, currency, tax registration). When those two concepts share one flat record, every change of role creates a duplicate, and a single organization that is both a customer and a vendor ends up as two disconnected records with conflicting addresses.

The mature design for this is the party model, which Microsoft documents in detail for Dynamics 365 Finance & Operations under its Global Address Book. In that model a party is a person or organization involved in the business, and the party can play more than one role — customer, vendor, contact, worker — based on context. Microsoft's guidance is explicit that using the party concept lets an entity play multiple roles, that party properties (name, language, contacts, addresses) are stored globally, and that a change in one place appears everywhere the party is involved. The Global Address Book is a centralized repository for the master data of all internal and external persons and organizations, with each party carrying one or more roles and one or more addresses. This is a genuinely powerful pattern: the who is defined once; the roles hang off it.

For an SME not on F&O, the same principle applies as a design discipline even where the platform's data model is flatter. In Business Central and Odoo the customer and vendor are separate records by default, but you can enforce the party discipline by design: store the legal name, tax ID, and all addresses once; choose a single survivor key for deduplication (tax ID or normalized legal name, never free-text); and link role-specific data (credit, terms, currency) to the relationship rather than re-keying identity. The design test is whether the same real-world organization, when it becomes both a customer and a vendor, is recognized as one entity or silently duplicated — if your model cannot, the model is incomplete. Address structure is its own design decision: design addresses as a repeating structure (purpose, line1, line2, city, region, postal code, country) validated against ISO code lists, separate bill-to, ship-to, and remit-to purposes, and keep tax registration numbers on the party record — not in a free-text note — because they drive every invoice's tax calculation.

Financial Dimensions: Where Management Reporting Lives

Financial dimensions are the management-reporting axes that slice every transaction without forcing you to create more accounts. They are the mechanism that makes a thin chart of accounts viable, and getting their design right is the difference between a finance team that can produce any segment report in seconds and one that spends a week each month in spreadsheets. In Dynamics 365 Finance & Operations, Microsoft documents that you can include up to ten additional financial dimensions in an account structure, that the structure defines which dimension values are valid in combination, and that dimensions come in two flavours: custom dimensions (shared across legal entities, values maintained by users) and entity-backed dimensions (values drawn from a master record such as a customer, vendor, or project).

The most important design rule for dimensions, and the one Microsoft's FastTrack architecture team warns about most strongly, is to avoid high-cardinality dimensions. A dimension's cardinality is the number of distinct values it can take: Department has low cardinality (a few dozen values), Customer has very high cardinality (thousands or tens of thousands). Using a high-cardinality entity like Customer or Item as a financial dimension is a classic error that bloats the ledger, degrades posting performance, and creates combinations impossible to validate. Microsoft's design-principle guidance specifically flags high-cardinality dimensions as a problem and recommends pushing that detail to sub-ledgers instead. The disciplined design keeps dimensions to low-cardinality management axes — Department, Cost Centre, Region, Business Unit, Project family — and lets the sub-ledgers carry the customer- and item-level detail.

Balancing dimensions are another design lever. Microsoft documents that you can optionally designate one financial dimension as the balancing dimension, so that whenever transactions post to it the system automatically generates balancing entries to keep that dimension in balance. This is the mechanism that lets you produce a complete, self-balancing balance sheet by legal entity (or by business unit) without manual intercompany entries. Choose the balancing dimension deliberately during design — for most multi-entity SMEs it is the legal entity — because it determines the structure of intercompany accounting for the life of the system. Default and fixed dimensions on the main account and on master records control how dimension values are inherited and whether they can be overridden, and these defaults are part of the design, not a configuration afterthought.

Odoo approaches the same problem through Analytic Plans, which since version 17 provide a genuinely multi-dimensional structure. Where older Odoo allowed a single analytic account per journal line, Analytic Plans let you define multiple independent tracking dimensions — a Department plan, a Project plan, a Region plan — each with its own analytic accounts, and tag a single line across several plans at once. Each plan carries an applicability rule (Optional, Mandatory, or Unavailable per journal type), supports parent-child subplans, and supports analytic distribution to split a line across accounts by percentage. Functionally this is the equivalent of Dynamics 365's dimensions, and the same low-cardinality discipline applies. For needs that outgrow dimensions, Microsoft's Financial Tags add up to twenty user-defined fields on transactions, and Odoo supports custom fields per model — reserve these for transactional metadata (an event code, a campaign) that should never pollute the chart of accounts. The test is always the same: structural and recurring belongs in the COA or a dimension; situational belongs in a tag. Whichever platform you are on, the dimension model must be designed before a single transaction posts.

Reference Data and Numbering Schemes

Around the four core entities sits reference data: the lookup tables and code lists that every master record and transaction references. Units of measure, tax codes, payment terms, posting groups, currencies, price lists, shipping methods, and reason codes all belong here. Reference data looks trivial because each list is small, but it is the connective tissue of the model — a misaligned tax code or a duplicated unit of measure ripples through every transaction. Design reference data as platform-wide, standardized lists with a single owner per list, defined once and reused everywhere, rather than letting each department invent its own payment-terms or tax-code variants.

Numbering schemes are the part of the model that most often gets defaulted to whatever the system suggests, and is then regretted. Every account, item, customer, vendor, document, and transaction needs a numbering strategy, and that strategy should reserve ranges for growth and for manual entry. A sound scheme blocks out number series — for example, customers auto-numbered from CUST-10000 upward, vendors from VEND-10000, items from ITEM-100000, with leading ranges reserved for manually created or imported records. Dynamics 365 uses configurable number series in Business Central and number-sequence frameworks in Finance & Operations; Odoo uses configurable sequences per model and document type. The design choice is not whether to number, but whether the numbers are meaningful (a coded structure that encodes type or region) or purely sequential (opaque but unambiguous). For most SMEs, sequential with a short type prefix is the most maintainable; meaningful coded numbers look clever at design time and become a maintenance burden within two years.

External IDs and legacy keys deserve an explicit design decision because they determine how cleanly the migration and any future integration will work. Decide during design which legacy unique key will become each record's stable identifier, prefix it to avoid collisions (legacy_customer_123), and store it in a dedicated field or the platform's external-id mechanism so re-imports are idempotent. Odoo's importer treats the External ID column as the key for create-or-update behaviour, and the equivalent discipline applies in Dynamics 365's Data Management Framework. Designing the key strategy up front is what lets the migration team iterate safely; leaving it to the migration phase is how records get duplicated.

Concrete hierarchy examples prevent abstract workshops from shipping vague schemes. For items, a three-level product hierarchy (Category → Family → Style or Model) with sequential SKUs under a short type prefix — for example FG-100001 finished goods, RM-200001 raw materials, SV-300001 services — keeps reporting rollups independent of the SKU itself. For partners, prefer sequential numbers with a role-neutral party key where the platform allows it (PARTY-10001) and hang customer or vendor roles off that identity; if the platform forces separate customer and vendor series, use matching external IDs and a shared tax-ID survivor rule so the same legal entity is still reconcilable. Reserve leading blocks in every series for historical or manually created records (for example CUST-00001–CUST-00999 reserved, auto from CUST-10000) so imports never collide with day-one operational numbering.

Designing for Multiple Companies, Currencies, and Consolidation

Even if the business runs a single legal entity today, the master data model should be designed as if a second company, a new currency, or an acquisition is plausible within its lifetime, because retrofitting multi-entity support into a single-entity model is one of the most expensive changes you can make. The design question is which master data is shared across legal entities and which is entity-specific. Oracle's guidance for global operations is to implement a corporate chart of accounts and assign it to primary ledgers, because a common corporate chart eases consolidation at group level for all subsidiaries. The same applies to the item and reference data: a shared product master and a shared tax-code table reduce consolidation friction, while entity-specific customer and vendor records reflect that a customer of one company is not automatically a customer of another.

Shared-versus-local is the decision matrix most multi-entity designs get wrong by defaulting everything to shared or everything to local. Shared masters (corporate chart of accounts, product catalogue, units of measure, payment terms, tax code catalogue) cut consolidation friction and prevent each subsidiary inventing parallel codes for the same concept. Entity-local masters (customers, vendors, bank accounts, sometimes price lists and warehouses) reflect that a commercial relationship and credit exposure usually sit with one legal entity. Hybrid is common for products: a shared product definition with entity-specific release, costing, and tax settings. Document the matrix in the design pack so migration, integrations, and future acquisitions inherit one rule set instead of tribal knowledge.

Currency is a design decision, not a setting. Each legal entity needs an accounting currency, each customer and vendor needs a transaction currency, and the model must store both the transaction-currency and accounting-currency amounts on every monetary record, plus the exchange rate used. Decide at design time whether multi-currency is in scope (most growing SMEs should assume yes) and build the gain/loss accounts and revaluation process into the chart of accounts from the start. Adding multi-currency to a model designed single-currency after a year of transactions is a reconstruction project, not a configuration change.

Consolidation-readiness is the ultimate test of the model. A model built with a shared chart of accounts, a consistent dimension structure, and a balancing dimension per legal entity makes a consolidated group balance sheet and P&L a standard period-end routine. A model where each entity invented its own account numbering during implementation turns consolidation into a manual mapping exercise every month. Designing for the day the business has two entities — even if it is three years away — costs almost nothing at design time and saves enormous pain later. When the model is structurally sound, the handoff into data governance and data migration is clean, because both downstream workstreams inherit a model that can actually hold the business.

Legacy-to-Target Mapping Checklist for the Model

A signed master data model is incomplete until it is expressible as a field-level map from every legacy system of record. Migration teams fail when they receive a conceptual COA and item hierarchy but no rule for which legacy code becomes the survivor, which fields are mandatory on load, and which free-text junk is deliberately dropped. Build the mapping checklist during design workshops — not after extract scripts are written — so cleansing rules and load templates share one definition of the target model.

For each core entity, capture four artefacts. First, the target field list with data type, length, mandatory flag, and allowed values (including account type, item type, costing method, dimension values). Second, the legacy source field and transformation (trim, case fold, ISO country, UOM conversion factor, account roll-up). Third, the survivor and match keys: tax ID and normalized legal name for parties, manufacturer part number plus brand for traded items, legacy account code plus type for the COA. Fourth, the reject and quarantine rules — records that fail mandatory validation must not invent new target codes on the fly. Store the stable legacy key in an external-ID or dedicated field so reloads are idempotent and cutover rehearsals can rerun without duplicates.

Reference data maps deserve their own sheet because they unblock every transactional load. Map legacy tax codes, payment terms, UOM symbols, posting groups, and reason codes to the standardized lists you designed earlier; do not let migration invent synonyms. Dimension and analytic-plan values should be pre-loaded and frozen before open-item or balance migration so historical postings land on valid combinations. When multi-company is in scope, add a company column to every map and flag which masters are shared loads versus per-entity loads. The output of this checklist is the contract the data migration playbook executes against — design defines the target; migration only fills it.

Where Design Ends and Governance and Migration Begin

The master data model sits at the boundary of two other disciplines, and one of the most useful things an implementation team can do is make the boundary explicit. The design phase — the subject of this guide — ends when the structure is signed off: the chart of accounts is numbered and typed, the item hierarchy and variant axes are chosen, the customer party model is defined, the dimensions are named and cardinality-checked, and the numbering schemes and reference data are laid out. At that point the structure is fixed, and two other workstreams take over.

The first is data governance: the ongoing discipline of assigning owners, running stewardship workflows, and enforcing quality rules that keep the data inside the model clean. Governance does not redesign the model; it operationalizes it, converting design decisions into enforced entry-validation rules, dedup survivor keys, and monthly quality scorecards. The dimension cardinality rule becomes a governance rule that blocks a high-cardinality entity from being added as a dimension; the party survivor key becomes the dedup rule the stewardship workflow runs weekly. If you want to understand the operating model, roles, and 90-day roadmap that keep the model's data trustworthy after go-live, that is the subject of the ERP master data governance guide.

The second is data migration: the one-time move of records from legacy systems into the now-designed model. Migration consumes the model as an input — every field map, transformation, and reconciliation check is defined against the structure the design phase produced. A well-designed model makes migration dramatically easier, because clean segment structures, consistent numbering, and stable external IDs give the migration team deterministic targets to map to. The extraction, cleansing, mapping, loading, and validation pipeline that moves the records is a separate discipline, covered in the ERP data migration playbook. The relationship is simple: design builds the container, migration fills it once, governance keeps it clean forever.

In 2026 the handoff carries an extra obligation: the model must be AI-ready, not only report-ready. Gartner has predicted that through 2026 organizations will abandon 60% of AI projects unsupported by AI-ready data — fragmented, poorly governed master records are a primary reason agentic workflows and forecasting fail after the demo. Practitioners put it more bluntly: AI agents will not fix wrong BOMs, stale lead times, or dirty supplier records; they will be confidently wrong at higher speed. That is a design problem as much as a stewardship problem. Stable keys, explicit attribute semantics, low-cardinality dimensions, and a party model that does not fork identity are what make downstream AI and analytics safe to automate. Treat those invariants as non-negotiable in the sign-off checklist, then hand the living quality rules to governance.

Seven Ways a Master Data Model Gets Designed Wrong

Most failed models fail in a small number of predictable ways, and naming them during the design workshop is the cheapest way to avoid them. The first is the overgrown chart of accounts — hundreds of accounts that encode department, project, or region into the account number because nobody trusted dimensions to do the job. The symptom is a GL that grows every quarter and resists clean consolidated reporting. The fix is a thin GL with management detail pushed into dimensions, applied at design, before the accounts multiply.

The second is high-cardinality dimensions — using Customer, Item, or Vendor as a financial dimension because someone wanted customer-level profitability in the GL. Microsoft's FastTrack guidance explicitly warns against this; it bloats the ledger, slows posting, and creates unmanageable combination counts. The fix is to keep dimensions low-cardinality and let sub-ledgers carry entity-level detail. The third is variant explosion — representing every colour, size, and configuration as a separate SKU rather than variants of a parent item, producing an item master nobody can maintain. The fix is a disciplined variant-axis design with two or three axes and attributes for everything else. The fourth is the flat customer master that mixes identity and role, so the same organization exists as a customer and a vendor with conflicting addresses — fixed by the party-model discipline. The fifth is no numbering scheme, defaulting to the platform's suggestions with no reserved ranges and no way to tell a migrated record from a manually created one. The sixth and most strategic is designing only for today — a single entity, a single currency, today's account count — so the first acquisition or new currency triggers a remediation project costing more than the original implementation. The fix is the scalability principle: build the model for the business you will be in three years, not just the one you are in today.

The seventh failure is shipping design without a legacy mapping contract — a beautiful COA and item hierarchy on slides, but no survivor keys, external IDs, or reference crosswalks when migration starts. The symptom is duplicate customers on every rehearsal load, inventing SKUs for the same part number, and last-minute account proliferation to match unclean legacy codes. The fix is the mapping checklist above: no design sign-off without field dictionaries, match keys, and reject rules. A related variant of the same failure is treating configurator-grade option spaces as stocked variants, which recreates SKU explosion under a modern UI. Name both in the workshop; they are cheaper to prevent than to remediate after open balances post.

Frequently asked questions