Getting Started with SQL

Chapter 9. Database Design

Planning a Database

So far in this book, we have only learned how to be consumers of data with the SELECT statement.  We have done analysis operations that read data and transform it in interesting ways, but none of this physically changes the data in the tables. A SELECT statement is a read-only operation. Sometimes, though, we will want to CREATE new tables, as well as INSERT, UPDATE, and DELETE records.

When you create your own tables to support your business, it should not be done lightly. You need to plan carefully because bad database design is sure to cause regrets down the road. There are critical questions that should drive your design:

Design questions

• What are the business requirements?

• What tables will I need to fulfill those requirements?

• What columns will each table contain?

• How will the tables be normalized?

• What will their parent/child relationships be?

It might be a good idea to draft a diagram showing the tables and how they are related. But design is not the only factor to consider. Populating data should be part of the planning process too. If the data is not maintainable and kept up to date, then the design has already failed. This factor is often overlooked and can easily cause a database project to fail.

Data questions

• How much data will be populated into these tables?

• Who/what will populate the data into these tables?

• Where will the data come from?

• Do we need processes to automatically populate the tables? ...