Chapter 5. Building a Multitable Relational Database

In this chapter, I take you through an example of how to design a multitable database. The first step to designing any database is to identify what to include and what not to include. The next steps involve deciding how the included items relate to each other — and setting up tables accordingly. I also discuss how to use keys, which enable you to access individual records and indexes quickly.

A database must do more than merely hold your data. It must also protect the data from becoming corrupted. In the latter part of this chapter, I discuss how to protect the integrity of your data. Normalization is one of the key methods you can use to protect the integrity of a database. I discuss the various normal forms and point out the kinds of problems that normalization solves.

Designing a Database

To design a database, follow these basic steps (I go into detail about each step in the sections that follow this list):

  1. Decide what objects you want to include in your database.

  2. Determine which of these objects should be tables and which should be columns within those tables.

  3. Define tables based on how you need to organize the objects.

    Optionally, you may want to designate a table column or a combination of columns as a key. Keys provide a fast way to locate a row of interest in a table.

The following sections discuss these steps in detail, as well as some other technical issues that arise during database design.

Step 1: Defining objects

The first ...

Get SQL For Dummies®, 7th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.