Logical Data Modelling

Description

• A method of identifying the critical entities (or general categories of information) required by the business processes that need to be supported by information technology systems.  Elements of a logical data model include an entity relationship diagram (ERD) that pictorially defines relationships between  entities expressed in terms of cardinalities (i.e. the number of occurrences of one entity relative to another entity) and supporting information, usually generated from a CASE (Computer Aided Software Engineering) tool, which contains definitions for each entity, as well as detailed definitions of all of the data elements—or attributes—which must be captured to fulfil the business requirements of the application.

When to Use

• The technique of building a logical data model is utilised during the Build phase of the BPI methodology and is preceded by the Data Architecture in the “To-Be” Technology Architecture (also commonly called a Conceptual or Enterprise Data Model) and is followed by the design of the physical database(s), if applicable, and their implementation during the Business Solution Roll-out in the Implement phase.

Approach

• Logical data models represent the data created or used by an organisation from the user perspective.  Through the later phases of  the BPI methodology, the audience for a logical data model shifts from the end users of the proposed system to the technical staff who will design and build it.  Consequently, as the model’s level of detail and complexity increases, the user presentation focus is also changing.  Process models are created, refined and completed in tandem with logical data models.  In the case of custom software development, these two models act as the primary inputs into the physical database design activity—the former showing how the end users work with (or process) their data, and the latter documenting the business requirements governing data creation, update, deletion and fundamental data relationships.  As with any structured modeling technique, the goal of designing a logical data model is to remove ambiguity and to promote consistent communication by utilising a commonly understood, standardised format.  

1. Complete the entity relationship diagram.
2. Normalise the data.
1. Define and document data elements of the data model
2. Define and document data entities
3. Define and document data attributes
4. Define and document data relationships
5. Define and document data volume estimates
3. Conduct a “walk though” of the completed Logical Data Model.

Guidelines

Problems/Solutions

• A common criticism of logical data models is that ‘analysis paralysis’ prolongs their completion.  Data modeling is an abstract process that is inherently non-deterministic.  Ensure that the project team and client discuss and document specific criteria by which the model will be judged ‘complete enough’.

Tactics/Helpful Hints

• Know that there is significant value in periodically updating the information in a logical data model to incorporate the data-related changes associated with corresponding application enhancements and fixes.  Keep logical data models  up-to-date to preclude significant rework  when systems are redesigned and/or integrated.
• Do some up-front research to identify the best approach to confirm the detailed information in a Logical Data Model with the client users.  The complexity of the diagramming notation and the volume of supporting documentation for the model is frequently confusing to the end-user.
• Periodically validate the logical data models that are developed against corresponding process models that have been previously developed.  This validation may result in the incorporation of detailed data requirements into process flows or detailed process requirements into data object definitions.
• Decide whether or not to use CASE (Computer Aided Software Engineering) tools.  If the choice is made for the automated CASE tool as opposed to a manual approach, make additional decisions about the scope of CASE tool usage.  For example, a full life cycle CASE approach would require trained project team staff, existing client data/database design standards, a preferably-networked CASE tool and supporting data-dictionary repository, a pictorial data/database diagramming and reporting capability, and database  script-generation functionality.

Resources/Timing

• Recognise that the same organisaion users who are targeted to explain an application’s business processes  are also needed to supply business data information for a logical data model.   Identify at least one client contact from each functional area (e.g. HR or Finance department) affected by the proposed application.
• Ensure that most application development projects have a lead project team member responsible for collecting, analysing and translating business requirements data into a logical data model, even though he or she may coordinate the efforts of other data analysts.  Since any application’s process and data requirements are closely intertwined, the project’s data analyst(s) and process analyst(s) work in tandem to synchronise and share their business requirements information.  Later in these projects,  ensure that the lead data analyst and the project team and/or client DBA (Database Administrator) work very closely to translate the logical data model into the physical database design

Examples

Sample Logical Data Model

Booking/Forecasting  System

• Introduction
• This data model represents all data produced as a result of capturing bookings in the Booking system module. The Booking module captures the details of a booking, notifies the region or area of the booking, obtains equipment-availability verification, confirms the booking for the customer and issues a release of equipment.  Since the Booking is a reservation for equipment, it will provide input for the Forecasting module.  The Forecasting module is a channel to provide consolidated customer and management forecasts to the Reposition/Optimization module.

Entity Descriptions

• Booking (shipping transaction)
• A set of past, present or future (a confirmation for a reservation of inventory) movement trans -     transactions for equipment under a specific booking associated to a particular contract.  A booking may be for equipment and/or service
• Booking Line Item
• The requested equipment on a Booking by booking ID, depot ID (origin and destination), inventory number and release number (the last two would be entered at a later point in time).  A line item exists in the database for “each” specific piece of equipment, even though it may be displayed as one line item (i.e. displayed as one line “10  20’ Dry Containers”  but stored as 10 separate records, since each will have it’s own equipment ID, depot location and release number)
• Booking State
• The past and present states of a specific booking.
• Company Organisation
• The organisation hierarchy of a company for a specific Company Classification Purpose (region, area, depot, location, container center, etc.)
• Contract
• An agreement between a company and a customer for a product or set of products.
• Inventory
• The equipment that exists within our company’s inventory systems (by serial number).
• Release Number
• An authorisation to the depot for release of equipment to a customer for a specific booking (there can be multiple Release Numbers per Booking Line Item).

Booking and Forecasting System Logical Data Model:

Templates

How to read an ERD: