Schema Mapping: Database Guide With Examples

Schema mapping defines how 5 structural levels move from one data model to another: entities, attributes, data types, constraints and relationships. A mapping is not complete because columns have similar names. It is complete when the target model preserves the business meaning, identifiers and behavior the source records represented.

Use this guide when a migration, integration or data conversion changes database structure. For value-level transformations, defaults and exceptions, use our source-to-target data mapping guide. For project phases and approval gates, use the data migration framework.

What is schema mapping?

Schema mapping is the specification that connects a source schema to a target schema. It records how source tables or objects correspond to target entities, how attributes align, how types and constraints change, and how keys and relationships remain valid after the move.

Structural level	Question the mapping answers	Example
Entity	Which source object becomes which target object?	customer becomes Account and Contact
Attribute	Which fields carry each property?	email_addr becomes Contact.Email
Data type	Can the destination represent the value safely?	text date becomes timestamp with timezone
Constraint	What is required, unique or restricted?	email optional in source, required in target workflow
Relationship	How do records remain connected?	orders retain the correct customer reference

Database schema mapping is common in platform migrations, application consolidation, cloud database moves, data warehouse loads and API integration. The same structural questions apply even when the source is a CSV file and the target is a SaaS object model.

Schema matching and schema mapping are different

Schema matching proposes correspondence. Schema mapping records the approved structural action.

A matching process may suggest that cust_no and AccountNumber refer to the same concept. The schema mapping still needs to state whether the match is accepted, whether the value is unique, how its type changes, which records lack it and whether dependent orders use it as a business key.

Activity	Output	Human decision still required?
Schema discovery	Inventory of objects, fields, types and constraints	Yes, undocumented behavior must be confirmed
Schema matching	Candidate pairs with confidence	Yes, similar names may have different meaning
Schema mapping	Approved structural crosswalk and rules	Yes, owners approve splits, merges and exclusions
Schema transformation	Executable target structure or load logic	Yes, output must be tested against business behavior

How do you create a schema mapping?

1. Capture the documented and observed schemas

Export the source and target definitions. Include table or object names, fields, types, lengths, precision, required flags, default values, primary keys, foreign keys, unique constraints and indexes.

Then profile representative data. A declared integer field may contain text from an old import. A documented one-to-many relationship may behave as many-to-many in production. Schema discovery must compare design with actual values before mappings are approved.

2. Establish the business entity model

Name the real entities represented by both systems: customer, contact, order, product, invoice or location. This prevents teams from matching fields solely because labels look alike.

Record whether each source entity maps one-to-one, splits into several targets, merges with another source, becomes a child object or is excluded. Define the system of record for overlapping entities.

3. Map attributes and data types

For each attribute, record source and target names, definitions, types, lengths, allowed values, required status and transformation class. Check for:

• text shortened from 500 to 255 characters;
• decimal precision or currency scale changes;
• local dates converted without a timezone rule;
• booleans represented as Y/N, 1/0 or several status codes;
• enumerations whose source values do not exist in the target;
• optional source fields that become required target properties.

Do not accept silent truncation or implicit type coercion. State the action: transform, reject, quarantine, complete from an approved source or revise the target design.

4. Preserve keys and relationships

Identify natural keys, source technical keys and target-generated keys. When target IDs are new, create an identifier crosswalk and define where it is stored, how long it is retained and which child loads depend on it.

Document cardinality explicitly: one-to-one, one-to-many or many-to-many. Name junction tables, parent load order and orphan-record handling. Referential integrity must be a test condition, not an assumption.

Worked database schema mapping example

An older CRM has one customer table. The target CRM separates companies into Account and people into Contact. Orders must reference the new account identifier.

Entity-level mapping

Source entity	Target entity	Pattern	Structural decision
customer	Account	Split	Company attributes form an account record
customer	Contact	Split	Person attributes form a contact linked to the account
sales_order	Order	Direct with new key lookup	Source customer key resolves to target account ID
customer_tag	AccountTag junction	Many-to-many	One account may retain several controlled tags

Attribute and constraint mapping

Source	Target	Type or constraint change	Mapping action	Validation
customer.customer_id integer	Account.Legacy_ID__c text, unique	Numeric to text; target ID generated separately	Preserve as business key	No duplicate legacy IDs
customer.company_name varchar(120)	Account.Name text(255), required	Capacity increases; required in target	Direct after trim	Blank company routes to review
customer.email varchar(255)	Contact.Email email	Format constraint added	Normalize and validate	Invalid value logged, not silently changed
sales_order.customer_id integer	Order.AccountId reference	Foreign key becomes target reference	Resolve through ID crosswalk	No orphan orders
customer_tag.tag_code varchar(20)	AccountTag.TagId reference	Code becomes controlled reference	Lookup tag master	Unknown code quarantined

This example shows why schema mappings and field rules are linked but not identical. The structural map decides that one source entity splits into Account and Contact. The detailed data mapping document specifies how each value is transformed, defaulted or rejected.

Common schema mapping patterns

Pattern	Use case	Main risk	Required control
One-to-one	Equivalent source and target entity	False equivalence	Compare definitions and sampled values
Rename	Same meaning, different label	Name-based match hides meaning change	Business definition approval
Split	One source entity becomes several targets	Lost linkage	Crosswalk and relationship tests
Merge	Several sources become one target	Duplicate or conflicting records	Survivorship and source priority rules
Flatten	Parent-child structure becomes one table	Repeated values and row multiplication	Grain definition and count tests
Normalize	Flat source becomes related tables	Missing keys or load-order failures	Generated-key and referential tests
Pivot or unpivot	Columns become rows or rows become columns	Wrong grain	Expected row-count formula
Exclude	Obsolete structure does not move	Accidental data loss	Named owner and retention decision

How should schema transformation be controlled?

A schema transformation changes the model so the destination can store and use the information. Keep the transformation declarative where possible: name the source structure, target structure, pattern, dependency and expected result.

Version the schema definition and mapping together. A target change can invalidate a field type, required flag or relationship even when mapping code has not changed. Each accepted change needs impact review and regression tests.

For automated or AI-assisted matching, retain:

• the candidate match and confidence;
• the evidence used to propose it;
• the reviewer and disposition;
• the final structural rule;
• the tests proving the approved mapping.

Low confidence is not the only risk. A high-confidence name match can still be semantically wrong. Human review should start with required fields, keys, regulated data and relationships that affect downstream processing.

How do you validate schema mapping?

Validation needs more than a successful load.

Validation layer	Check	Failure it detects
Definition	Every in-scope entity and attribute has a disposition	Unmapped structures
Type	Values fit target type, length and precision	Truncation and coercion
Constraint	Required, unique and allowed-value rules hold	Invalid target records
Key	Business and technical identifiers remain traceable	Duplicate or unresolvable entities
Relationship	Parent-child links and cardinality match expectations	Orphans and wrong joins
Volume	Counts reconcile by entity and filter	Missing or duplicated records
Behavior	Target workflows, reports and integrations work	Structurally valid but unusable data

Use representative records for every mapping pattern, plus nulls, boundary lengths, duplicate keys, unknown codes and missing parents. Compare expected and actual outputs. Store the result against a stable mapping ID.

For a zero-downtime database migration, repeat the checks after the initial load, during change capture and after cutover. Reconcile the final delta before writes move to the target. The migration plan must also define rollback conditions and the maximum acceptable lag.

Schema mapping failures to catch early

Mapping labels without definitions. type and status are common names with system-specific meanings.

Ignoring target-required fields. A source-first review can miss destination properties that need a valid input or approved default.

Treating keys as ordinary fields. New IDs can break orders, contacts and histories unless the crosswalk and load order are designed first.

Checking rows but not relationships. Counts may reconcile while children point to the wrong parent.

Combining matching with approval. Suggestions save review time, but ambiguity, exclusions and model changes still need accountable owners.

Letting schema versions drift. Code built against an old target definition can pass unit tests and fail at release.

Schema mapping review checklist

Before approval, confirm:

• The source and target schema versions are named.
• Every entity has a direct, split, merge, derived or excluded decision.
• Required target attributes have valid inputs or approved defaults.
• Type, length and precision changes have explicit controls.
• Primary keys, foreign keys and identifier crosswalks are documented.
• Cardinality and junction structures are defined.
• Load order and orphan handling are testable.
• Sensitive fields retain the correct classification and access controls.
• Each structural mapping has representative test data.
• Business owners approve meaning; technical owners approve execution.

FAQs about schema mapping

What is an example of schema mapping?

A customer table may split into Account and Contact objects while orders resolve the new Account ID through an identifier crosswalk. The mapping records the entity split, attribute correspondence, key handling and relationship tests.

What is the difference between schema matching and schema mapping?

Schema matching proposes which elements correspond. Schema mapping records the approved structural action, constraints, transformations, ownership and validation requirements.

Is schema mapping the same as data mapping?

No. Schema mapping focuses on entities, attributes, types, keys and relationships. Data mapping adds field-level value rules, lookups, defaults and exceptions.

Can schema mapping be automated?

Discovery and candidate matching can be automated. Owners still need to resolve ambiguous meanings, required fields, split and merge decisions, keys, cardinality and exclusions. Automated suggestions should be reviewed and versioned.

What tools are used for schema mapping?

Teams may use spreadsheets, data catalogs, ETL tools, integration platforms or repository-based specifications. Choose based on mapping volume, change rate, collaboration, audit needs and whether mappings feed code or tests.

How do you test referential integrity after migration?

Check that every child reference resolves to a valid parent, expected cardinality holds, no unintended duplicates exist and representative end-to-end records behave correctly in the target system.

If your source and target models are known but splits, keys or relationships remain unresolved, Acelerar can scope the schema mapping, conversion and test work. Discuss the migration with our data team and bring both schema exports plus representative records.