Q

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

Learn two common approaches to converting a data model from a conceptual design to a logical design.

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

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

More about logical data models

This was first published in April 2008

Dig deeper on Data modeling tools and techniques

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close