Database Normalization Tips

Summary: This article provides developers with the skills to avoid certain problems when designing an Access table. This article applies to Microsoft Access databases (. mdb) and Microsoft Access projects (. adp).

Brief introduction

When designing a database, the most important step is to ensure that the data is correctly distributed to the tables in your database. With the right data structure, you can greatly simplify other content in your application (queries, forms, reports, code, and so on). The formal name for proper table design is "database normalization".

This article briefly introduces the basic concepts of database normalization and some common problems that need to be noticed and avoided.

Understand your data

Before you design a table, you should be clear about how you intend to work with the data, and how the data will change over time. The assumptions you make will affect the final design.

What kind of data do you need?

When designing an application, it is critical to understand the end result of the design to ensure that you have all the necessary data ready and know its source. For example, the appearance of the report, the source of each data, and whether all the data you want exists. The biggest loss to the project is the lack of data at the end of the project to find important reports.

Once you know what kind of data you need, you must determine the source of the data. Is the data imported from another data source? Does the data need to be cleaned or validated? Does the user need to enter data?

Defining the type and source of data you need is the first step in database design.

What do you intend to do with this data?

Does the user need to edit this data? If so, how should the data be displayed for the user to understand and edit? Are there validation rules and related lookup tables? Are there any audit issues associated with the need to edit and delete data entry for the reserved backup? What summary information do you want to display for the user? Do you need to generate an export file? Once you know this information, you can imagine how the fields are interconnected.

How do data relate to each other?

Group data into related fields (such as customer-related information, invoice-related information, and so on), and each field group represents the table to be established. Then consider how these tables are related to each other. For example, which tables have a one-to-many relationship (for example, a customer might hold multiple invoices)? Which tables have a one-to-one relationship (in this case, you'll typically consider grouping them into one table)?

What happens to the data over time?

After you design a table, you will often have serious problems after you have not considered the impact of time. Many table designs work very well at that time, but they often crash as users modify data, add data, and over time. Developers often find it necessary to redesign the structure of the table to accommodate these changes. When a table's structure changes, all related content (queries, forms, reports, code, and so on) must also be updated. Understand and predict what the data will change over time, and you can achieve better design and reduce the occurrence of problems.

Learn how to use queries

It is also important to know how to analyze and manage data. You should understand how the query works, how to use queries to link data across multiple tables, how to group and summarize data using queries, and how to use crosstab queries when you don't need to display data in canonical format.

The ultimate goal of good data design is to balance two needs: to efficiently store data over time, and to easily retrieve and analyze data. Understanding the functionality of a query can be helpful in designing a table correctly.

Database Normalization Concepts

This section introduces the basic concepts involved in database normalization, rather than the theoretical discussion of database normalization. How you apply these concepts to your actual situation may vary depending on your application needs. The purpose of this section is to understand these basic concepts, apply them to actual needs, and understand the problems that may arise from deviating from these concepts.

Store unique information in one place

Most database developers understand the basic concepts of database normalization. Ideally, you would want to store the same data in the same place and use an ID to refer to it when you need it. Therefore, if some information changes, you can make changes in one place, and the corresponding information in the entire program changes.

For example, a customer table stores records for each customer, including name, address, phone number, e-mail address, and other feature information. The Customer table may contain a unique CustomerID field (usually the Autonumber field), which is the primary key field of the table that other tables use to refer to the customer. Therefore, the invoice table can reference only the customer's ID value instead of storing all of the customer's information in each invoice (because the same customer may hold multiple invoices), so that the customer's ID value can be used to find the customer's details from the Customer table. You can easily do this by using powerful forms in Access, which use combo boxes and subforms. If you need to modify customer information (for example, a new phone number), simply modify it in the Customer table, and any other part of the application that references that information will automatically be updated.

With a properly normalized database, you can easily handle changes in data over time with simple editing. With improperly normalized databases, you typically need to use programming or queries to change multiple records or multiple tables. This not only increases the workload, but also increases the likelihood of data inconsistency due to improper execution of code or queries.