Access database optimization - the first formal forms

Proper database architecture goes aloft techniques abstruse in Microsoft Access courses. Experience and added training will be all-important for all but the a lot of basal database projects. The convenance of database optimization, aswell alleged database normalization, is a accumulating of best practices in database design. The a lot of basal set of these rules is alleged Aboriginal Normal Form or 1NF.

Don't Duplicate Fields

Microsoft Access courses advise the basal database filigree with anniversary row a almanac and anniversary cavalcade a acreage aural that record. In a database of sales representatives, anniversary row represents one of the sales people. Fields aural anniversary almanac are entries like "name" or "sales territory".

Assume aggregation sales territories are torn out by city. Anniversary city-limits has one rep assigned but anniversary rep has a altered amount of cities. John Smith ability awning Bristol while Mary Jones handles both Cardiff and Newport to accept a commensurable chump base.

It is a aberration to use assorted area fields such as City1 and City2. The aboriginal rep almanac would decay amplitude on a bare City2 field. Any city-limits queries would accept to seek both City1 and City2 fields. If a new rep is added with a 3-city territory, the absolute database would accept to be rebuilt with a new field.

The Wrong Solution: Combining Fields

An incorrect band-aid is to accept a acreage alleged "Territory" and account as abounding cities as needed. So John is assigned "Bristol" in this acreage while Mary has "Cardiff, Newport".

Although this seems to break the problem, it is still duplicative. The queries from your Microsoft Access advance would accept to be structured to seek locations of the acreage application wildcards and that agency slower response. Afterlight a acreage agency afterlight the absolute list, which could be difficult for a rep who serves a dozen baby towns.

The Right Solution: Assorted Annal Not Assorted Fields

Don't try to get duplicative advice into a individual record. Although it seems automatic to accept one almanac for anniversary employee, as continued as anniversary almanac is different a bigger advantage is to accept assorted records.

The aloft archetype would accept three records: John Smith - Bristol, Mary Jones - Cardiff and Mary Jones - Newport. Since the agent table is acceptable to accept added advice that there is no acumen to echo like salary, the area abstracts should be stored in a abstracted table.

In adjustment to hotlink the capital agent table with the area table, there needs to be a acreage in common. The accessible best is agent name. Obvious, that is, until a additional John Smith is hired.

As is accomplished in Microsoft Access courses, use an agent ID acreage in the capital agent table. Use that ID instead of agent name in the area table. It not alone ensures character of identifier, but cuts down on errors such as a concern for Mary Jones assuming alone one area because the added was assigned to "Mary Jones".