Approach to Database Design


Steps to Database Design

The material on this Page is laid out to reflect the way in which it would be generated from a Database.
This has resulted in additional format comments for clarification. We have also drafted Data-Centric Extreme Approach to Database Design and a User-Centric Extreme Approach.

In this General Approach we have defined a sequence of Steps in a structured method to design a Database.
You can have a look at this Page to see how this Approach applies to the design of a Database for an HR Department.

This Approach is based on establishing the Business Rules or FACTS in a sequence of structured Steps.

Chapter 1. Establish the Business Rules.

Section 1.1 Define the Scope of the Data Model.
Paragraph 1.1.1 Introduction

Line 1.1.1.1 The Scope is the Area of Interest, for example, the HR Department in an organization.

Line 1.1.1.2 Define the "Things of Interest",(e.g. Employees), in the Area of Interest.

Line 1.1.1.3 Analyze the Things of Interest and identify the corresponding Tables.

Line 1.1.1.4 Identify the Static and Reference Data, such as Country Codes or Customer Types.

Paragraph 1.1.2 Determine the characteristics of each Thing of Interest.

Line 1.1.2.1 For example, "an Employee has a Date-of-Birth".

Section 1.2 Establish the relationships between the Tables.

Section 1.3 Define a Range of User Scenarios.
Paragraph 1.3.1 Work with Users to define a range of representative User Scenarios.

Chapter 2. Design the Database.
Section 2.1 Define a Primary Key for all Tables.
Paragraph 2.1.1 Reference Data

Line 2.1.1.1 For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.

Paragraph 2.1.2 Other Data

Line 2.1.2.1 For all other Data, you can use a generated number as the Primary Key.
Line 2.1.2.2 This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
Line 2.1.2.3 However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
Line 2.1.2.4 It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be.
Which means, of course, that it often never gets done.

Chapter 3. Verify the design of the Database.
Section 3.1 Obtain a small set of Sample Data.
Paragraph 3.1.1

Line 3.1.1.1 For example, "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
Line 3.1.1.2 "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"

Section 3.2 Review the draft of the Database design against the Business Rules and Sample Data.

Section 3.3 Establish some representative enquiries for the Database.

Section 3.4 Ask the Users to define their 'Top Ten' Enquiries.
Paragraph 3.4.1

Line 3.4.1.1 - For example, "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"

Line 3.4.1.2 Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.

Line 3.4.1.3 For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.

Line 3.4.1.4 Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.

Section 3.5 Work through the User Scenarios with Users using some sample data to check that that Database supports the required functionality.

Chapter 4. Agree the Results with the Users
Section 4.1 This final Step is very important.
Paragraph 4.1.1 It usually takes several discussions to achieve concensus.

Line 4.1.1.1 Review the Results with appropriate people, such as Users, Managers,

Line 4.1.1.2 Development staff, etc. and repeat until the final Database design is reached.