Lesson2.1.Planning a Database

Lesson 2.1. Planning a Database

Figure 2-1. Break up information as much as possible: The same information stored in a poorly designed table and in a well-designed table.

Figure 2-2. Use multiple tables so that you don't duplicate information: The same information stored in a badly designed table and in a well-designed table.

Although you can always make changes to a database, a little ahead before you create a database can save you lots of time and headaches later on.

Consider Figure 2-1: In the first table, you can only sort by the name or address field. If you sort the name field, the sort is performed by the first name. If you sort the address field, the sort is performed by the streetyou cannot sort by city, state, or zip code. You couldn't create a query or filter that only displays people from a particular state because the states are not stored in their own field. The fields are not flexible.

Now take a look at the second table in Figure 2-1. Here you can sort records by first name, last name, address, city, state, and zip code. You can also query and filter records using any of these fields.

Here are some guidelines for creating a well-designed database (see also Table 2-1):

• Determine the Purpose of the Database: The best way to do this is to write down a list of the reports and lists that you want to come out of the database. This may seem a little backward at first, but if you think about it, these reports are really the reason you're creating the database. Make a list of the reports and lists you want to see and then sketch some samples of these reports and listsbe as detailed as possible. This will help determine the tables and fields to include in your database.

• Determine the Fields You Need: This should be an easy step once you have determined the purpose of your database and have sketched some sample reports and lists. Think about the data type for each type of your fieldsWill the field store text information? Numbers? Dates? Write down the data type next to each field.

• Determine the Tables You Need: Each table in the Database should be based on only one subject. By breaking each subject into its own table you avoid redundant information and make the database more organized. The second database in Figure 2-2 is broken down into two tables, Customers and Invoices, so there isn't any duplicated data. When you brainstorm, try to break down your information as much as possible. If your tables contain fields like Item 1, Item 2, Item 3, Item 4, and so on, you should probably break the information up into its own table.

• Determine the Primary Key: Each record in a table should have a primary key that uniquely identifies it. When you think about a primary key field, think unique each primary key value must be the only one of its kind in a table. A customer ID or invoice number would be two good examples of fields that could be used as a table's primary key.

• Determine the Relationship between Tables: In Figure 2-2, the ID field links the Customers and Invoices tables together. One of the linked fields should be the table's primary key.

• Sketch a Diagram of Your Database: Create a diagram of your database. Draw a box for each of your tables and write the table's field names inside that box. Draw a line between the related fields in the tables. For example, in Figure 2-2, each record in the Customers table is related to one or more records in the Invoices table.

Table 2-1. Guidelines for Good Database Design

Guideline	Why?
Each field or column should contain the same type of information	This makes the table more meaningful, more organized, and easier to understand.
Try to break up information as much as possible	This gives you more power to sort, filter, and manipulate the list. See Figure 2-1 for an example.
Use multiple tables so that you don't duplicate information in the same table	Organize your information into several tableseach one containing fields related to a specific subjectrather than one large table containing fields for a wide range of topics. See Figure 2-2 for an example.
Don't use duplicate field names	Duplicate field names can cause problems when entering and sorting information.