Planning the database design in Microsoft Access 2019

Planning is an extremely valuable step before you even think oflaunching the database program.

Follow these easy steps:

  1. Define your database clearly.
  2. Ask yourself what you would like to get out of using the database –for example, a weekly printout of students’ results, attendance, termlyreports, or school finances.
  1. Think about what information you will need to put (store) into thedatabase to get the results you want. Write down this information on ahuge sheet of paper. In order to produce results, you would need to planbackward from the result you would like to reach. Brainstorm the numberof tables you need, the purpose and fields within the tables, as well asthe field types (such as numeric, text, or date/time). Will you needcalculations to be performed? Will the information be sorted? Must thedata in different tables be connected via relationships?
  2. Plan your tables so that you are able to store your information anddecide on the fields you will need for each table. tables are thefoundation of the database. It will be easier to create other objects ifthe tables are set up properly from the outset.

For instance, if you were creating a database for a schoolenvironment, you would draw up some sample data with which to start. Ifyou look at the following table, you will notice that the information insix out of the eight columns is being repeated unnecessarily. Every timeyou would like to enter a student’s result, you will have to repeat thestudent’s details again, leading to a lot more wasted time by typingduplicate data. Not to mention a greater chance of making errors whencapturing data! You will also waste valuable space on the computer asthe database file will be considerably larger:

ms office 735

The problem with the preceding table can be solved by dividing theeighth column table into two tables, one for pupil information and theother for pupils’ marks. This way, information can beretrieved very quickly and can be linked by one common,unique field (a relationship) between the tables:

ms office 753

This relationship type would be considered aone-to-many relationship. This means that we wouldenter one student who will have manymarks recorded against their name.

You are also able to break up a field in a table. An address fieldcould be divided into street address, city, and postcode. When suchfields are divided, it is more beneficial as we are able to sort by dataindividually.

It is important to discuss the difference between data andinformation at this point. Information is the output that we receive outof a database. Data is the raw material, for instance, a number, word,or symbol. Data is facts used for reasoning and analysis. Data is storedin the database, not information.

Leave a Comment