Database Design

Performing the conversion of a Logical Data Model to a Physical Database Design has often troubled the data modeler and mystified the DBA. However, with the use of a few simple principles and several clearly defined stages, this conversion can be made manageable and understandable.

The first step is the definition of key empirical data. Four major factors need to be considered: Amount of data, Complexity of data, Complexity of queries, Load factor.

Guided by these factors, the designer can then make reasonable trade-offs to the data. The key is to balance these trade-offs.

Trade-offs can be divided into several classes, namely, technology, safe and aggressive trade-offs. Technology trade-offs, such as adding indices, do not modify the data structure but can help optimize the system. Safe trade-offs make compromises without jeopardizing integrity. For example, splitting a large table into several non-redundant tables, or collapsing trivial code tables. Aggressive trade-offs do compromise integrity but can provide big performance gains. Examples of these are adding redundancy and storing derived data.

An important message is that "there is not such thing as a free lunch". Trade-offs are just that. The key is to understand their cost and the impact. This seminar will use several practical examples to demonstrate a systematic and sensible way to achieve this.

Objectives 

On workshop completion, participants will be able to:

• How to perform the conversion of a logical data model to a physical datadata design
• Understanding the types of trade offs and their impact in database design

What Attendees Will Learn 

• Introduction to database design
• Transition from logical to physical
• Defining the automation boundary
• The first-cut physical model
• Denormalization
• Applying safe trade-offs
• Applying aggressive trade-offs
• Adding indices
• Defining integrity requirements
• Performing access path analysis
• Affect on the data model

Duration                 2 days

Course Format        Lecture, group discussion and exercises

Instructor                 Tom Haughey

 

© Copyright 2002 DebTech International, LLC. All Rights Reserved.