Database Design

From MIKE2.0 Methodology

Activity: Database Design

Objective

In this Database Design activity, the target data model is refined to a more detailed level (building off the conceptual design) to meet the detailed business requirements. This target modelling process derives directly from the Data Standards established in Phase 2. The Physical Model should derive from the Logical Model.

Major Deliverables

• Logical Data Model of Target System
• Physical Data Model of Target System

Tasks

Design Logical Data Model

Objective:

The purpose of this task is to formally integrate the information gathered to date in the form of a logical data model. Using the initial conceptual model, the next level of detail is to ensure that all attributes are fully defined, primary and foreign keys are specified and the model is fully normalised and cardinality is indicated. For warehouse implementations, logical modelling will take an additional step in the process in addition to the steps above. The additional design process will extend the model to introduce time variance, derived attributes, aggregates and stability.

The modelling approach will be different depending on whether relational or dimensional techniques are being employed. If, for example, this task was focused on dimensional modelling, then this task should include the following:

• Identify Dimensions
• Identify Measures
• Design Dimensional Hierarchies
• Define summarization levels
• Design Fact Tables, including Attributes and Measures (Star Schema design)
• Design Dimension Tables (Star Schema design)
• Design Fact (Summary) Tables (Star Schema design)
• Define Metadata Layer

There will not always be a conceptual model as input to the logical modelling process as not all projects define a conceptual model.

Input:

• Conceptual Data Model
• Data Specification Standards
• Data Modelling Standards
• Data Security Standards
• Detailed Business Requirements for Increment

Output:

• Logical Data Model

Develop Physical Data Model

Objective:

The purpose of this task is to use the logical model design as a basis to design the physical database. This task involves modelling the increment’s database structure. Considerations should be volume/cardinality, partitioning, indexing, and backup/recovery. The degree of changes from the logical model to the physical model will vary largely based on the type system that is being built. The physical design may also differ from the logical model to improve performance or simplify query complexity.

Input:

• Logical Data Model
• Solution Architecture
• Data Specification Standards
• Data Modelling Standards
• Data Security Standards
• Detailed Business Requirements for Increment
• Non-Functional Requirements

Output:

• Physical Data Model
• Database Definition Language (DDL) Scripts
• Sizing Estimates

Core Supporting Assets

Reference the MIKE2.0 Solution for Data Modelling, in particular:

• Guidelines for Validating a Logical Data Model
• Data Modelling Standards and Guidelines
• Guidelines for Using an Off-the-Shelf Data Model
• Data Vault approach

Yellow Flags

Following poor practices in modelling, such as:

• Not following established naming conventions
• Failing to populate data dictionary
• Database design processes circumvent use of modelling techniques and focus on definition through physical table structures

Key Resource Requirements

• Information Architect
• Data Modeller

Potential Changes to this Activity

• This activity may potentially be broken into 2 separate activities, one covering logical design the other covering physical design. It may also have more tasks added as opposed to detail being stored in Supporting Assets.

• Taxonomy Design, Data Modelling and Message Modelling are currently defined as separate activiites. It may be better to converge this to a single activity.