Hello, Welcome to Paul's learning curve! It's been quite a while since I had written a Blog. Now I promise to keep this consistent.
So I am here to help you out with some of the basic and simple tips and tricks with Access, that could make your life a little bit easier! The topic today is essential, and fundamental for any Database structure! This is available on YouTube !
As we have covered in my first post, JET is a Relational Database Management System. This means that the Database is dependent purely on Relationship Models.
Throughout this post I will be using a simple example of Student information, to help you understand the fundamental principles. Let’s begin!
Rule - 1: Every row in a table must be unique.
What it technically means by is that, consider a class of students, it is impossible to have two students who are exactly the same, to identify each student we use a unique identifiers in this case a Student ID, this is called the 'Primary Key'. The functionality of this Key is that it will not allow you to enter a record which will be a duplicate. Simple!
Rule - 2: Data should be "Atomic"
Data in its raw form might be clunky, in a Relational DB you should not be condensing information in one single column. Simple examples would be Address of a Student. The address would contain several bits of information, House number, City, County and Post Code. All these information are by itself separate and needs to be in their own columns as such.
Rule - 3 : Data should be single valued
Similar to Rule 2; although a bit different, let me explain with an example. If a student is taking up 3 major subjects, These subjects should not go in one column "Subjects taken" and store all the information in that column : Maths, Science, English. Instead they should go in a new table linking to every single student record, thus creating a relationship between the two tables.
Why you may ask? Well the reason is quiet obvious, if you create three columns Main subject 1, Main Subject 2, Main subject 3 and then you need to collect other information regarding the subject maybe course start date. If you keep adding new columns there will be three additional columns each for one subject, the more information you need the more columns you will end up adding. So this stage you create a new table, tbl_courseInfo specific to courses. As the DB structure grows you will understand that DB does not contain just one table, but several tables broken down and combined together forming the information we require.
Rule - 4: Data Redundancy
So as the words plainly mean, never have the same information in multiple tables, this will only cause trouble of what you are referring to. The simple example would be from Rule 3, all you need to link the two tables together is the ID, the Name and address can be obtained by simply JOINING them in Queries. There is no need to save the information on to the new table.
Rule - 5: Avoid calculations/dependent values
This is a major and very important aspect. Example could be Grading system. You should not create a Column in the table for this, if you need grades you calculate when and where required using Queries. Never store such calculations.
So the above five rules, simply dictate how a DB should be designed. This is the tip of the iceberg, there are several more to be discovered, Normalization being another huge topic. There should be plenty of tutorials available online for you to learn this.
So let’s finally recap: Five fundamental principles to a DB Design:
So I am here to help you out with some of the basic and simple tips and tricks with Access, that could make your life a little bit easier! The topic today is essential, and fundamental for any Database structure! This is available on YouTube !
As we have covered in my first post, JET is a Relational Database Management System. This means that the Database is dependent purely on Relationship Models.
Throughout this post I will be using a simple example of Student information, to help you understand the fundamental principles. Let’s begin!
Rule - 1: Every row in a table must be unique.
What it technically means by is that, consider a class of students, it is impossible to have two students who are exactly the same, to identify each student we use a unique identifiers in this case a Student ID, this is called the 'Primary Key'. The functionality of this Key is that it will not allow you to enter a record which will be a duplicate. Simple!
Rule - 2: Data should be "Atomic"
Data in its raw form might be clunky, in a Relational DB you should not be condensing information in one single column. Simple examples would be Address of a Student. The address would contain several bits of information, House number, City, County and Post Code. All these information are by itself separate and needs to be in their own columns as such.
Rule - 3 : Data should be single valued
Similar to Rule 2; although a bit different, let me explain with an example. If a student is taking up 3 major subjects, These subjects should not go in one column "Subjects taken" and store all the information in that column : Maths, Science, English. Instead they should go in a new table linking to every single student record, thus creating a relationship between the two tables.
Why you may ask? Well the reason is quiet obvious, if you create three columns Main subject 1, Main Subject 2, Main subject 3 and then you need to collect other information regarding the subject maybe course start date. If you keep adding new columns there will be three additional columns each for one subject, the more information you need the more columns you will end up adding. So this stage you create a new table, tbl_courseInfo specific to courses. As the DB structure grows you will understand that DB does not contain just one table, but several tables broken down and combined together forming the information we require.
Rule - 4: Data Redundancy
So as the words plainly mean, never have the same information in multiple tables, this will only cause trouble of what you are referring to. The simple example would be from Rule 3, all you need to link the two tables together is the ID, the Name and address can be obtained by simply JOINING them in Queries. There is no need to save the information on to the new table.
Rule - 5: Avoid calculations/dependent values
This is a major and very important aspect. Example could be Grading system. You should not create a Column in the table for this, if you need grades you calculate when and where required using Queries. Never store such calculations.
So the above five rules, simply dictate how a DB should be designed. This is the tip of the iceberg, there are several more to be discovered, Normalization being another huge topic. There should be plenty of tutorials available online for you to learn this.
So let’s finally recap: Five fundamental principles to a DB Design:
- Unique Records
- Atomic Data
- Single Valued
- Data Redundancy
- Dependent data.
