Flat file or Relational Databases?

At GCSE level most practical work involves flat file databases. Whilst it may be rasonable that practical work be limited it is my view that students should understand realtional database principles. Why? well students don't study ICT past GCSE level, most of the important databases they will be "on" that is those were their data is stored will not be flat file. It is therefore important to understand how relational databases work. Even if you don't have to make one.

What is a flat file database?

Well expressed simple a flat file database contains only one table so the British Birds database is a flat file database. Flat file databases have hundreds of uses. They are powerful tools and they are the right tools to use if the database involves only one data entity. Earlier we discussed a database for doctors appointments and said that this would require three tables; one for each data entity the three data entities here are the patient the doctor and the appointment. The table containing the appointment is likely to contain at least three fields one for the patients name, One for the doctors name, and one for the appointment time/date. (There might be other fields like the location of the appointment, or things the patient must remember to bring. Two of the three fields in this table should contain derived values. Take the example of the patients name. A badly designed database would have the receptionist simply type in the name of the patient. THIS IS ALWAYS WRONG!!

Why? because you then have two copies of the data in the database all sorts of errors can then arise. Lets look at a real example. Suppose Miss Smith has a condition that requires an annual checkup. The database is set up for five years in advance with five appointments. After 18 months Miss Smith marries and becomes Mrs Jones she and her husband move to a new home. She tells the surgery and the receptionist goes to the patient table and amends her name. The appointment tables are ignored six months after the wedding a letter is sent to Miss Smith at her former address. No one there knows who this is so the letter is thrown away. Mrs Jones then misses the important appointment - the consequences could be fatal (literally in this case).

How is this overcome? - by using a relational database. In a relational database the receptionist can't type directly into the field in the appointments table for the patients name. She selects the patient from a list that is part of the table. The list is taken from the original patients table. This is possible because the value is a derived value due to a relationship between the two tables. If the name and address are changed the changes are made in the original patient table and copied down to the other tables. So when the receptionist changes the name in the patients table the data is "cascaded down" to the other tables. In this case to the appointments table. The letter goes to the right address when it is printed.

© LEV