EXPERT RESPONSE
Depending upon your tool and modeling notation, your Conceptual Data Model (CDM) may already be an entity-relationship (ER) model. CDMs should, in most cases, be business models -- and not data design models (as is the Logical Data Model (LDM)), so your CDM should identify real world business objects (e.g. Customer, Order, Sale, Policy, etc) and the relationships between these. In other words, CDM's are not design -- they are used to describe the business. Data design comes into play in the LDM.
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
Four guidelines for enterprise conceptual data model (ECDM) entity selection
What are the benefits of a conceptual data model?
A guide to conceptual data models for IT managers
More about logical data models
Logical data models and normalization
|
