Planning Data Bases
As you can probably imagine, planning a Data Base before creating it will save us lots of problems and headaches. If we don't, we might find duplicate data, inconsistent Relationships, or even the possibility of not getting reliable Reports.
The first step is to determine what the information in our table will be used for and thus add the fields we will need to get and manage that information. If we don't have the aspect clear, the problems in our tables will increase exponentially as we move forward, up to the point we might need to re-structure our design. For example, the "Clients" table should only include information related to our clients, and nothing more.
At first glance, it might seem we need to know what he purchased, how much money he paid for it, who the vendor was, etc. However, this information is not related exclusively to our clients. It is also related to our Articles table (what he purchased) and with the Invoices Table (how much did he pay). If we were to add this information in this table, we would find duplicates in other tables. This is a clear symptom that our table is not properly designed.
One of the problems of having such a potent and versatile tool as Access is the fact that we can perform many tasks. This includes designing a Table with the fields we need, even if it's not efficient. We might find out it is poorly designed after we obtain an error in some Report. Nevertheless, if we follow these steps, we can create well-designed Tables.
The next step is to decide on the fields we will include in our table. You might think this is an easy task after deciding on the use of our table, but it is more delicate than you might think! For example, in our "Clients" Table, we know what we want to save: the name, address, and telephone number. Are these 3 fields the ones we want to see in our table?
As you can see, we have used the fields "Client's First Name" and "Last Name", also the fields for "Address" and "ZIP code". Why have we done this? We have done this to sort and group the records for each field. If we didn't separate the First and Last names, we wouldn't be able to organize the information in relation to their Last Name. In the case of the Address field, it is better to add a field for the ZIP code to organize this information in a better way.
There are no concrete rules on how to determine our fields properly. Experience is the best way to learn your way. However, there are some customs which might ease this task; for example: ask yourself if you will be accessing a specific field. If the answer is "yes", you might want to divide it into more fields.
Create all the fields you need, but make sure they serve only the purposes of this specific Table. For example, we don't need to introduce the information on our vendor in the "Clients" table. You can create a table for them if you like.
Finally, you don't need to create fields which will calculate form others. It is more useful and accurate to perform mathematical operations whenever they are needed. For example, in case we have a date of birth, we don't need another field with the age. It is more effective if we calculate this when necessary instead of updating it year after year.
