From MIKE2 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
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:
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:
Core Supporting Assets
Reference the MIKE2.0 Solution for Data Modelling, in particular:
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
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.
BlogMarks
del.icio.us
digg
Furl
Simpy
smarking
