How to connect business understanding with technical precision by transforming conceptual data models into logical blueprints within a metadata-driven framework — Inspired by Alec Sharp’s Business-Oriented Data Modelling Masterclass
🧩 Metadata-Driven Modeling Series
A series exploring how modern data platforms connect business meaning and technical implementation using metadata-as-code.
1️⃣ From Business Concepts to Database Blueprint — bridging conceptual and logical models inspired by Alec Sharp.
2️⃣ Linking Conceptual and Logical Data Models in Metadata — maintaining traceability and automation between business concepts and implementation models using YAML.
3️⃣ Generating Logical Models Automatically from Conceptual Metadata — (coming soon): implementing Alec Sharp’s normalization patterns (1NF–2NF–3NF) in Python.
Summary
Data modeling is often mistaken for a purely technical activity — a prelude to database design. In reality, it is first and foremost a business exercise: an exploration of what an organization cares about, what it tracks, and how it defines relationships between key concepts.
Alec Sharp, in his Business-Oriented Data Modelling Masterclass, masterfully demonstrates how the Conceptual Data Model (CDM) and Logical Data Model (LDM) play distinct but connected roles in this process.
In our metadata-driven approach — where models are expressed in YAML files in VSCode — these distinctions become not only theoretical but practical. We maintain clear folder structures for conceptual, logical, and physical layers, allowing each to evolve independently yet remain connected through generation scripts and metadata rules.
1. The Conceptual Data Model — The Business Worldview
The Conceptual Data Model (CDM), sometimes called the Business Object Model or Concept Model, captures the business vocabulary and worldview — the nouns and relationships that business stakeholders naturally use when describing their domain.
Alec Sharp defines it elegantly:
“A conceptual model is simply a description of a business in terms of the ‘things’ it deals with.”
The CDM focuses on understanding, not implementation. Its purpose is to clarify what matters to the business and how those things relate, before introducing any technical structure.
Key characteristics of a Conceptual Model:
- Business Vocabulary: Only includes recognizable business concepts — singular nouns such as Customer, Order, Product.
- Minimal Attributes: Illustrative rather than exhaustive; attributes are often non-atomic (e.g. “Full Name” instead of “First Name” + “Last Name”).
- Abstraction and Simplicity: Entities are plotted top-down by dependency to make diagrams easy to read.
- M:M Relationships Allowed: Many-to-many relationships are fine — we don’t yet worry about normalization.
- No Technical Detail: No keys, optionality, or data types.
A good conceptual model fits on one page — it’s a communication artifact, not a specification. In fact, Alec often jokes that it represents 20% of the modeling effort but 80% of the understanding.
2. The Logical Data Model — Adding Rigor and Structure
Once the business concepts are clear, we shift gears toward the Logical Data Model (LDM) — the detailed, fully normalized blueprint that defines how data will be structured and related in a system.
The LDM extends the CDM by introducing precision: attributes are atomic, relationships are resolved, and every dependency is made explicit.
Key characteristics of a Logical Model:
- Granular Entities: Roughly five times as many entities as in the conceptual model, including associative and characteristic entities.
- Atomic Attributes: Each attribute holds one fact, fully defined and validated.
- Normalization: Typically 3rd Normal Form (3NF) or higher.
- Resolved Relationships: All M:M relationships are replaced with associative entities.
- Keys and Rules: Every entity has primary, foreign, and alternate keys defined, along with cardinality (“may be” vs. “must be”).
This model forms the bridge between business meaning and technical implementation — and represents roughly 80% of the total modeling effort.
3. From Conceptual to Logical — The Transformation Rules
The art of transforming a Conceptual Model into a Logical Model lies in introducing structure while preserving meaning.
Alec Sharp describes this process as systematically resolving three kinds of attributes — each representing a normalization step.
Step 1: Resolve Multi-Valued Attributes → 1NF
When an attribute can hold multiple values (e.g., an Employee has multiple Contact Points), it must be moved into a dependent entity.
Technique:
Create a Characteristic entity (if it describes one parent) or an Associative entity (if it describes a M:M relationship).
Result:
The model now satisfies First Normal Form (1NF) — data is reportable and structured for repetition over time.
Step 2: Resolve Redundant Attributes → 2NF
If a fact is repeated in multiple entities (e.g., Company Name appearing in every Department record), it should be moved up to its rightful parent.
Technique:
Move the attribute to the “one” side of the M:1 relationship.
Result:
Entities now reflect a single source of truth for each fact — achieving Second Normal Form (2NF).
Step 3: Resolve Constrained or “Sideways” Redundant Attributes → 3NF
When an attribute’s values are restricted to a fixed set (e.g., Employee Type = “Full-time”, “Part-time”), it should be moved to a reference or lookup entity.
Technique:
Create a Reference entity linked via a M:1 relationship.
Result:
Attributes now describe only their own entity, reaching Third Normal Form (3NF).
4. Applying These Ideas in a Metadata-Driven Framework
In our model-driven architecture, these same principles apply — but they’re expressed as metadata rather than diagrams.
We maintain three folder levels in VSCode:
/models
/conceptual
customer.yaml
order.yaml
/logical
customer.yaml
order.yaml
customer_contact.yaml
/physical
customer.sql
order.sql
- Conceptual YAMLs capture the business vocabulary (entity names, high-level relationships, example attributes).
- Logical YAMLs are generated from conceptual ones by applying normalization transformations (multi-valued → characteristic, redundant → parent, constrained → reference).
- Physical SQL models are generated from the logical layer for specific platforms (e.g., Databricks, Snowflake, Postgres).
This approach allows analysts to start with the business (the conceptual model) and evolve toward implementation-ready structures (the logical model), all within the same modeling environment.
It bridges the gap between Alec Sharp’s whiteboard clarity and modern metadata-driven automation.
5. Why This Matters
By explicitly modeling both conceptual and logical layers — even in YAML — we ensure that:
- Business and IT share the same vocabulary.
- Changes cascade systematically from business concepts to physical design.
- Model generation becomes auditable and reproducible.
- Governance and metadata lineage stay intact across layers.
The conceptual model becomes the why, the logical model the how, and the physical model the where — all connected by traceable metadata.
In Summary
The journey from Conceptual to Logical data modeling is not just about normalization — it’s about clarity, alignment, and structure.
By applying Alec Sharp’s timeless patterns to a metadata-driven modeling framework, we can combine the best of both worlds: business-friendly conceptual thinking and code-driven automation.
In the end, every YAML file is just another way to tell the story of the business — but with precision, lineage, and the ability to generate anything from it.
🔗 Next in the Series
Linking Conceptual and Logical Data Models in Metadata — how to maintain traceability and automation between business concepts and implementation models using YAML.