Database Answers Header Steps to Peacock Pond, Florida
Steps to Database Design
Home Ask a Question Careers Data Models FAQs SQL Scripts Search Site Map  

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

  1. Line The Scope is the Area of Interest, for example, the HR Department in an organization.
  2. Line Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
  3. Line Analyze the Things of Interest and identify the corresponding Tables.
  4. Line 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.

  1. Line 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

  1. Line 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
  1. Line For all other Data, you can use a generated number as the Primary Key.
  2. Line This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
  3. Line However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
  4. Line It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be.
  5. 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
  1. Line For example, "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
  2. Line "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

  1. Line - For example, "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
  2. Line Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
  3. Line For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
  4. Line 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.

  1. Line Review the Results with appropriate people, such as Users, Managers,
  2. Line Development staff, etc. and repeat until the final Database design is reached.

[ Home Page | Ask Us a Question | Email Us | FAQs | Search | Site Map ]

© IceBreaker WebDesigns 2002