There are different schools of thought regarding converting your CDM to LDM. The traditional and most common approach entails:
- Identifying all applicable entities (CDM doesn't express all the details)
- Fully/mostly attributizing data entities (with business nomenclature)
- Assigning datatype domains (e.g. text, date, numeric) vs. datatypes (varchar, integer)
- Resolving M:M relationships (e.g. with an associative entity, record versioning, etc)
- Formalizing keys (primary, alternate, foreign)
- Resolutions of subtypes (3 methods for resolution)
- Performing abstraction (e.g. abstracting conceptual entities such as Customer, Prospect, Supplier, etc. into a generalized entity such as Party) as part of the normalization process (so that data can be stored once)
This approach follows the definition of the LDM as "provable by the mathematics of data science." (Applied Information Science website)
Another approach is to make the LDM largely an attributized CDM, with the resolutions above taking place in the Physical Data Model. The advantage is that a single LDM could have many physical manifestations, e.g. for a normalized online transaction processing (OLTP) application or as a denormalized dimensional data mart, and the meta data relationships are automatically maintained. This approach is more appropriate for enterprise models as there can be a wide degree of applicable situations where the entity may be required.
The downside is that complexity is increased, clarity may be decreased, and in many shops a first-cut PDM is created by the Data Architect/Modeler and handed off to the DBA for further changes for performance and maintainability (with the review and approval of the DA/Modeler!). When this occurs the LDM and PDM might be maintained in separate files -- thus minimizing the data lineage benefits.
More about conceptual data models
More about logical data models
This was first published in April 2008