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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Data architecture / Data modeling Data architect vs. application architect: Segregate the duties Enterprise versus project level conceptual data modeling XML schema definition versus conceptual data model Four guidelines for enterprise conceptual data model (ECDM) entity selection Data modeling for data warehouse projects Data architecture vs. information architecture What are the benefits of a conceptual data model? Data modeling tools and techniques Advantages and disadvantages of XML shredding How to shred XML with the DB2 XMLTABLE function Shredding XML docs into relational tables with annotated XML schemas Examples of single and bulk XML shredding of XML documents Improving ODBC application performance and coding How to capture metadata information, ETL rules with CA Erwin Data Modeler Data Warehouse Platforms Product Directory Data models serve as blueprint for business intelligence, master data management projects Similarities and differences between ROLAP, MOLAP and HOLAP Data modeling for the business: What is a data model?

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data modeling  (SearchDataManagement.com) predictive modeling  (SearchDataManagement.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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