4. Conceptual Overview

Database Design for Mere Mortals: A Hands - on Guide to Relational Database Design

What does this chapter cover?

1. The general idea of the steps involved within the design process

2. They are covered in detail in subsequent chapters

What are these steps useful for?

1. The design steps can be used for either designing a new DB from scratch or refactoring an existing one

2. These steps are mandatory regardless of the size, type, or purpose of the database

3. The steps must be followed from beginning to end

What are these steps in brief?

1. Defining a missing statement and missing objectives

2. Analyzing the current database

3. Creating the data structure

4. Determining and establishing the table relationship

5. Determining and defining the business rules

6. Determining and defining views

7. Reviewing data integrity

What is "Defining a missing statement and missing objectives"?

1. Missing statement - Why are you building this database? Answering provides design focus

2. Mission objective - What can users do with your database?

What is "Analyzing the current database"?

1. Note existing mode of database (paper files, other DB, etc.)

2. Note mode of data collection, representation, and making it available to external customers (if any)

3. Interview stakeholders of the database data on how it's used

4. The answers obtained in (3) are used to define the initial list of fields on which stakeholders’ feedback is requested. The list is nowhere finalized, just a starting point.

What is "Creating the data structure"?

1. Define tables and fields

2. Establish keys

3. Define field specifications for every field

What is "Determining and establishing the table relationship"?

1. Conduct interviews again to identify the relationship

2. Relationship characteristics (Type and Degree of participation)

3. Establish relationship-level integrity (Foreign keys and keeping records in sync)

What is "Determining and defining the business rules"?

1. This phase imposes additional constraints on data, table, and relationship based on interviewing stakeholders again.

2. The constraints are defined based on ways organization perceives that data

3. The rules are intended to be changed as the business evolves

What is "Determining and defining views"?

1. Conduct interviews once again to identify how the information is consumed by different users e.g. some may require a detailed list, others might need just summary

2. Views are defined for ease of data access and security purposes

What is "Reviewing data integrity"?

1. This is essentially reviewing the output of the above steps and bug (inconsistency) fixing

2. Review and check the table structure, field specifications, relationships, and then business rules

Is the process complete by performing these steps?

1. Yes and No. This is an iterative approach.

2. As business evolves the database needs refinement.

***