For a proper database design you need a little more than just the knowledge obtained in MS Access training. Getting more experience is necessary for all database projects, except the most basic ones. Database optimization or also known as normalization is a set of the best practices of designing the database. One of the sets of these rules is called First Normal Form and it is one of the most basic ones.

The first rule is not to duplicate fields. You learn the basic database pattern on MS Access courses, where there are rows, records, fields, etc. So, if you have a database of sales representatives, every row will represent one sales person. And fields in each record will store the information like name of the person, etc.

Sales territories can also be displayed in the fields and let’s say that the sales territories of the company are broken down by city. So, each sales representative can have several cities assigned but every city has only one rep. That is why it would be a mistake to use multiple fields like City1 and City2 for them. If a sales person has one city, the City2 filed will be blank and it will waste space. All city queries will have to look for information on both City fields. And if the sales representative with three assigned cities appears, you will have to rebuild the whole database.

Combining fields is not the right solution either. You shouldn’t create one “Territory” field and enter as many cities as you want there. It might seem like a solution to a problem, but the field is still duplicative.

The right decision is to create multiple records and not fields. Don’t enter duplicative information into one record. It seems logical to have one record for each employee, but in this case it is better to have multiple records, only if they are unique. So you would create separate records for different cities, even if they have the same rep. Usually, territory records should be stored in a separate table, as the employee table will have other information you don’t have to repeat. Then you can link the territory table with the employee table, but in order to do that you need to have a field in common. In order to avoid further errors, it is not recommended to use the employee name as the common field, in case, another employee with the same name is hired.

A good solution would be having IDs for all employees in the main employee table. You should use that ID in the territory table instead of the employee name. It will prevent errors in queries and provide uniqueness to the identifier.

The world of computers and computer applications expands all the time. But you can turn this issue into your strong point by catching up with this knowledge and becoming a pro, for example in microsoft access database niche.

Please visit this site that is dedicated to microsoft access database topic and has a nice line of microsoft access database products available.

Tags: microsoft access, microsoft access database