Proper database design goes beyond the techniques learned in Microsoft Access courses. Experience and more training will be needed for all but the most basic database projects. The database optimization practice, 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 sales rep database, each row represents one of the salespeople. The fields within each record are entries such as “name” or “sales territory”.
Assume that the sales territories of the company are broken down by city. Each city has an assigned representative, but each representative has a different number of cities. John Smith could cover Bristol while Mary Jones manages Cardiff and Newport to have a comparable client base.
It is a mistake to use multiple territory fields, such as City1 and City2. The first repeating record would waste space in a blank City2 field. Any city query would have to search the City1 and City2 fields. If a new representative is added with a territory of 3 cities, the entire database should be rebuilt with a new field.
The wrong solution: combine fields
A wrong solution is to have a field called “Territory” and list as many cities as necessary. So John is assigned “Bristol” in this field, while Mary is assigned “Cardiff, Newport”.
Although this seems to solve the problem, it is still a duplication. Your Microsoft Access course queries would have to be structured to find parts of the field using wildcards and that means a slower response. Updating a field means updating the entire list, which could be difficult for a rep serving a dozen small towns.
The correct solution: multiple records, not multiple fields
Do not try to get duplicate information in 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 example above 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 such as salary, the territory data should be stored in a separate table.
To link the main employee table to the territory table, there must be a common field. The obvious choice is the name of the employee. Obvious, that is, until a second John Smith is hired.
As taught in Microsoft Access courses, use an Employee ID field in the main Employees table. Use that ID in place of the employee’s name in the territory table. Not only does it guarantee the uniqueness of the identifier, but it reduces errors like a query for Mary Jones showing only one territory because the other was assigned to “Mary Jones”.
