Break up information as much as possible: The same information stored in a poorly designed table and in a well-designed table.
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.
Consider Figure: 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 street you 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. 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:
-
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 lists be 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 fields Will 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.
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 tables each one containing fields related to a specific subject rather 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. |