Database Answers Canonical Data Model (Click for details)
Home Ask a Question Best Practice Careers Contact Us Data Models Search Site Map
Types of Data Models   
We would be glad to have your comments.

If you are new to Data Models, this page of my Tutorial will help you understand the Data Model.
There are no generally agreed definitions of the different types of Data Models that are in common usage. Click here to see our current Definitions.
We summarise here the current consensus among Data Modelling practitioners.
Semantic Models combine meaning (Semantics) and Graphic Presentation to add value to other types of Data Models.
Conceptual Models are used to establish agreement with business users about
the most important 'Things of Interest' and Subject Areas in the business. I use them when it is important to describe the areas of a business are being affected.
This helps the user community to understand an Enterprise Data Model.
A Semantic Layer helps to translate terms like 'Party' to user-friendly equivalent, such as 'Customer' or 'Supplier'.
It is valuable if the Semantic Layer is to be agreed with the business community.
Wikipedia has a definition of Semantic Data Model which is a more general usage of ours.
and also has an interesting definition of Conceptual Schema which is worth a look.
Conceptual Models
A Conceptual Model for Participants and
Inheritance created in Microsoft Word

Shown as an ERD

Here is our example of a Conceptual Model for Airport Management :-

  • Logical Model (LDM) is where Data Modellers come into their own and add significant value.
    Subject Areas are LDMs for specific functiomal areas, such as Reservations and User Workshops will be held to obtain User Buy-in.

    Here is our LDM for Airport Management :-

  • A Physical Model (PDM) is usually generated in one of two ways - either by 'reverse engineering' from a physical Database or automatically by the Modelling Tool, such as ERwin or Dezign, that was used to design the Logical Data Models.
    The design of the PDM will usually change to reflect the denormalization of the original LDM to achieve improved performance.
    The LDM is then preserved as a record of the Requirements and the PDM becomes the solution to the Requirements.
    The rest of this page discusses the different Types of Logical Data Models that commonly occur :-
  • BI Layer
  • Semantic Layer
  • Data Marts / Dimensional Models (Star and Snowflake)
  • 3NF Data Warehouse
  • Staging Area/ODS Models

    Examples are shown below.
    This list can be used as a Template to carry out an Assessment of a specific Modelling situation in an organisation.
      Here is our current draft list of the different Types of Data Models

    BI Layer Presents the data required for BI Analysis and Reports.The Model generally looks like the Semantic Data Model, in the next level.
    A BI Dashboard monitoring the Environment in Local Government ...

    BI in Local Govt
    Data Model for the Dashboard on the left ...

    Data Model for BI in Local Govt
    A BI Dashboard monitoring the OnTime Delivery (OTD) Performance of individual Stores is shown here ...

    BI Dashboard for Monitoring Stores Performance
    Data Model for the Dashboard on the left ...

    Data Model for Stores OTD Performance Monitoring
    Semantic Models present a 'Business User Friendly' view of the the data. For example, talking about 'Customers' instead of 'Parties'.
    These two examples help to explain.
    Semantic Data Model for Customers, Orders and Products ...

    Data Model for Customers Sales
    Semantic Data Model for Suppliers, Deliveries and Products ...

    Supplier's Deliveries
    Data Marts Present the restricted sets of data required for specific Report families. They usually have the same Dimensions and Facts structure that we find in Dimensional Models.
    Data Mart for Customers, Orders and Products ...

    Data Mart for Customers Sales
    Data Mart for Suppliers, Deliveries and Products ...

    Data Mart for Deliveries
    Dimensional Models
    Here is an example of a Generic Data Mart :-

    Generic Data Mart
    A Data Warehouse in Third Normal Form helps to ensure a Single View of the Truth.
    It can be a subset of the more comprehensive Enterprise Data Model.
    This is an example of a 3NF Data Warehouse for the Canonical Data Model :-

    Third Normal Form for the Canonical Data Model
    Enterprise Data Model (EDM)
    EDMs can be one of two types - a very large Model with perhaps hundreds of Entities or a smaller Model with details in Subject Area Models.
    Here is an example of an EDM for a Retail business ...

    Retail EDM
    Subject Area Data Models Pretty much essential - in other words, if you don't find one, then there is something wrong.
    We also designed this Data Model for Deliveries.
    This will support our Deliveries Data Mart

    Deliveries Data Model
    From the ODS, we have derived the design of a 3NF Data Model for the Customer Sales Subject Area.
    Customer Sales Data Model
    Operational Data Stores can be in a range of formats - from a simple CSV file, to an ODBC connection, to a bespoke SAP or Salesforce interface.
    Here are my wife and myself on a recent holiday in Malaysia riding on an Elephant, with the "ticket to ride" (ie the ODS) and the ODS Entity :-

    Elephant Ride Ticket for the Elephant Ride
    Here is the Data Model for the Malaysian Generic Sales Receipt ...

    Generic Sales Receipt for Malaysia

    Barry Williams
    Principal Consultant
    Database Answers Ltd.
    London, England
    March 4th. 2013

  • © DataBase Answers Ltd. 2013
    About Us Contact Us