Access Database Optimization: First Normal Form

Proper database design goes beyond techniques learned in Microsoft Access courses. Experience and further training will be necessary for all but the most basic database projects. The practice of database optimization, also called database normalization, is a collection of best practices in database design. The most basic set of these rules is called First Normal Form or 1NF.

Don't Duplicate Fields

Microsoft Access courses teach the basic database grid with each row a record and each column a field within that record. In a database of sales representatives, each row represents one of the sales people. Fields within each record are entries like "name" or "sales territory".

Assume company sales territories are broken out by city. Each city has one rep assigned but each rep has a different number of cities. John Smith might cover Bristol while Mary Jones handles both Cardiff and Newport to have a comparable customer base.

It is a mistake to use multiple territory fields such as City1 and City2. The first rep record would waste space on a blank City2 field. Any city queries would have to search both City1 and City2 fields. If a new rep is added with a 3-city territory, the entire database would have to be rebuilt with a new field.

The Wrong Solution: Combining Fields

An incorrect solution is to have a field called "Territory" and list as many cities as needed. So John is assigned "Bristol" in this field while Mary has "Cardiff, Newport".

Although this seems to solve the problem, it is still duplicative. The queries from your Microsoft Access course would have to be structured to search parts of the field using wildcards and that means slower response. Updating a field means updating the entire list, which could be difficult for a rep who serves a dozen small towns.

The Right Solution: Multiple Records Not Multiple Fields

Don't try to get duplicative information into a single record. Although it seems intuitive to have one record for each employee, as long as each record is unique a better option is to have multiple records.

The above example would have three records: John Smith - Bristol, Mary Jones - Cardiff and Mary Jones - Newport. Since the employee table is likely to have other information that there is no reason to repeat like salary, the territory data should be stored in a separate table.

In order to link the main employee table with the territory table, there needs to be a field in common. The obvious choice is employee name. Obvious, that is, until a second John Smith is hired.

As is taught in Microsoft Access courses, use an employee ID field in the main employee table. Use that ID instead of employee name in the territory table. It not only ensures uniqueness of identifier, but cuts down on errors such as a query for Mary Jones showing only one territory because the other was assigned to "Mray Jones".