Data model conversion: Conceptual design to logical design using an ER model

Can you give me a scenario converting a data model from conceptual design to logical design by using entity-relationship (ER)?

    Requires Free Membership to View

    Login

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

    • This was first published in April 2008

    Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.
      Back to top