Saturday 7 December 2013

Fundamentals of Database Design

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:

  • Unique Records
  • Atomic Data
  • Single Valued
  • Data Redundancy
  • Dependent data.
If you follow this simple 5 step rule, I am sure you will not be going wrong in any aspect of your design. If in doubt make sure you ask for help !

2 comments:

  1. Paul, The info above looks good even if I could not make much out of the tiny image. I will be taking a database design class next semester. However, since this seems to be one way to reach you, I will say I saw several of your posts about hyperlinks. I am looking for a simpler way to create hyperlinks, not to open them. If a user puts a file via Internet Explorer into a subfolder of a subfolder of a subfolder, is there a way to use VBA in Access 2010 to let you click on the file in the IE window to fill in most of the path from the file up to a certain level such as \\ancna01\Anchorage_Share\Data\Transportation\? Currently, to create a hyperlink to a file that they just added to the server, they have to open the hyperlink dialog box and navigate to the file that they can already see in the IE window. They really would like to tell Access to create a like by asking what file in the IE window should be linked. We manually add the server path prefix (above) to the beginning of the path to replace \..\.. or H:\ so that we can open the file from any machine despite the inconsistency of drive letters. Just some VBA to add the path would be much appreciated because, I for one, often forget to copy the path prefix ahead of time so I can paste it into the dialog box as part of the path. I have noticed that when I forget to copy the path prefix a bunch of %20 characters show up, replacing the spaces in the path, using up some of my 255 character limit in a path. A way to replace %20 with spaces would be nice, too. I sure would like to be able to send you screen shots and such since I can tell from your posts you would be able to help in the last two items if not the first item. gsmith73@uaa.alaska.edu. The address is gmail, but the University of Alaska, Anchorage changes the gmail.com to look like uaa.alaska.edu or, also acceptable is alaska.edu without the "uaa." Greg Smith

    ReplyDelete
  2. Hello Greg, what you wish to do is not impossible. However I would have to tell you that it will take quite some time to give you the exact help you need. Specially without seeing you design and what you have done so far. I have done something similar in my apps. If you need some general help you can go on to www.access-programmers.co.uk. If you want a specific help please respond to my email.

    ReplyDelete