Data Modeling & Schema Design

A data model defines the entities, attributes, and relationships that describe what data means and how it fits together — before a single table is created.

At a glance

• What it is: The practice of moving from a conceptual model (business language, “a Customer places an Order”) through a logical model (attributes, keys, relationships) to a physical model (actual tables, columns, types, indexes for a chosen storage engine).
• Why it matters: The model doesn’t fail loudly when it’s wrong — it quietly produces confusing reports, duplicate data, and brittle integrations for years afterward.
• For developers: your ORM classes and migration files are a physical data model, whether or not anyone designed one deliberately — an undocumented model is still a model, just a hidden one.
• When to use it: Before persisting data for a new domain or system, and before designing a data warehouse’s star schema.
• When not to use it: As a substitute for API contract design — a data model describes storage shape, not the shape of a request/response payload (though the two should stay consistent).
• Prerequisites: A basic understanding of Data Architecture.

Mental model

graph LR
    A["Conceptual model\n(business language)"] --> B["Logical model\n(attributes, keys, relationships)"]
    B --> C["Physical model\n(tables, types, indexes)"]

Each level answers a different question: conceptual asks “what exists and how does it relate,” logical asks “what attributes and rules does each thing have,” and physical asks “how do we actually store this efficiently in the chosen engine.” Skipping straight to physical design is how naming and structure end up dictated by whatever the first engineer typed into a migration file.

Step-by-step walkthrough

1. Identify the core entities and what they mean in the real world, independent of any system.
2. Define relationships between entities and their cardinality (one-to-many, many-to-many).
3. Build the conceptual model in business language — no column types, no table names yet.
4. Refine into a logical model: attributes, primary/foreign keys, normalization rules.
5. Translate into a physical model: concrete tables, column types, indexes, and partitioning suited to the chosen storage engine.

Worked example

Northwind’s “Customer” entity is used by the CRM, the e-commerce storefront, and the support ticketing system — each with a slightly different set of attributes.

• Input: three systems, three different Customer shapes (CRM has a sales-rep field, storefront has a shipping-address list, support has a ticket-history reference), and no shared definition of what a “customer” attribute set should be.
• Process: build a conceptual model of the core Customer entity (identity, contact info, addresses) that all three systems agree describes the same real-world thing, then let each system extend it with domain-specific attributes rather than redefining the core.
• Output: a canonical logical model — Customer(customer_id, name, email, addresses[]) — that CRM, storefront, and support all reference, each adding their own extension attributes without duplicating or renaming the shared core.

// Shared core, referenced by every system
public sealed record Customer(Guid CustomerId, string Name, string Email, IReadOnlyList<Address> Addresses);

// Storefront-specific extension — doesn't redefine the core
public sealed record StorefrontCustomer(Customer Core, IReadOnlyList<Guid> SavedCartIds);

Physically, the core Customer table lives in Azure SQL Database as the system of record; each consuming system’s extension attributes stay in its own store, joined back to the core by CustomerId.

Common use cases

Use case	When to use	Notes
New system design	Building a new application from scratch	Model before you migrate, not after
Data warehouse star schema	Designing fact and dimension tables for analytics	Physical model here is deliberately denormalized for query performance
Cross-team schema alignment	Multiple teams reference the same entity	Conceptual model gives a shared vocabulary before physical implementation diverges

Advanced details

• Normalization vs. denormalization: normalize in OLTP/logical models to avoid update anomalies; denormalize deliberately in analytical/physical models (e.g. star schemas) for read performance.
• Star vs. snowflake schema: a star schema keeps dimension tables flat for simpler, faster queries; a snowflake schema normalizes dimensions further, trading query simplicity for reduced redundancy.
• This is the Kimball vs. Inmon debate: Ralph Kimball’s dimensional modeling approach builds denormalized star schemas bottom-up, one business process at a time, favoring fast delivery and query simplicity; Bill Inmon’s approach builds a normalized enterprise data warehouse top-down first, then derives dimensional marts from it, favoring a single consistent enterprise model over faster individual delivery. Most real warehouses are a pragmatic mix rather than a pure implementation of either.
• Schema evolution: treat schema changes as versioned contracts — additive changes (new nullable column) are usually safe, renames and type changes are breaking and need a migration plan for every consumer.
• Physical modeling isn’t just relational: for a relational engine (Azure SQL Database, Azure Database for PostgreSQL), physical modeling means tables, types, indexes, and normalization. For a document store like Azure Cosmos DB, the equivalent physical-modeling decision is the partition key — chosen for even write distribution and to keep the queries a workload actually runs within a single partition; a badly chosen partition key (“hot partition”) is the Cosmos DB analog of a missing index.

Quick reference

Term	Meaning
Conceptual model	Business-language description of entities and relationships
Logical model	Attributes, keys, and relationships, independent of storage engine
Physical model	Concrete tables, types, and indexes for a specific storage engine
Normalization	Structuring data to eliminate redundancy and update anomalies
Star schema	Denormalized fact/dimension design optimized for analytical queries
Kimball / Inmon	The two named methodologies behind the dimensional-bottom-up vs. normalized-top-down warehouse design debate
Partition key	Cosmos DB’s physical-modeling equivalent of an index/normalization choice

Troubleshooting

Symptom	Likely cause	Fix
Same entity modeled differently across teams	No shared conceptual model was agreed on first	Facilitate a conceptual modeling session with all consuming teams before building
Reports need constant complex joins	Logical model over-normalized for an analytical use case	Introduce a denormalized physical model (e.g. star schema) for the analytical workload
Every schema change breaks a downstream consumer	No schema versioning or contract discipline	Treat schema changes as contract changes — version and communicate them

Check your understanding

• Why is a physical model not a substitute for a conceptual model when onboarding a new team member?
• Take a table you know well and try to describe its conceptual model in one sentence of business language — does it match what the columns actually store?

Related pages

Part of Introduction to Data Architecture. See also Master Data Management for how canonical entity models stay consistent across systems, and the Glossary for term definitions.