Database Components  «Prev  Next»

Lesson 7 Designing a database
Objective Organize a database to hold data so that the data can be used for analysis.

Designing a Database

While I certainly do not expect you to be an expert at designing complex databases, I do want to give you a little background on how to get started. Before you begin creating a database it is important to spend a little time planning its design. It can be awkward and time consuming to change a database’s design after you have begun to use it. Generally the most important aspect of the database design is the tables. You will have to break down the data for the database into tables. Each table should focus on one topic.
Once you have determined the topic for each table, you can decide which fields will be in the table. A rule of thumb is that each field should relate directly to the topic of the table. You want each field to contain the smallest meaningful piece of data. Splitting data into its smallest meaningful part gives you the most flexibility for analysis. If you find yourself entering redundant information within a single table, you may want to reconsider the design of your tables. For instance, if you have designed a table like the one below (Original Table Design) where customer information is repeated, you should redesign it to break the same information into two different tables (Client Table and Project Table).

Original Table Design
First Name Last Name Address Project
Margaret Levine 455 Cherry St. Home Office
Margaret Levine 455 Cherry St. Dining Room
Catherine Molkenbur 25 Pine St. Living Room

New Design

Client Table
Client NumberFirst NameLast NameAddress
1MargaretLevine455 Cherry St.
2CatherineMolkenbur25 Pine St.

Project Table

Client NumberProject
1Home Office
1Dining Room
2Living Room

Repeating information in a table (such as the names in the table shown here) can lead to mistakes during data entry and may make analysis more difficult. Want to try your hand at creating a new database? It is not so hard, just follow the steps in the next lesson.