Relational databases versus flat file databases
There are problems with flat file databases. However, there is something that can be done about it! Having two smaller tables is no different to having one big table, as long as we link them (or ‘relate’ them - hence the term ‘relational database’). Consider one record in a table. We can have one big record in one table, or split the big record into two parts and link them together (using a special link called a 'foreign key'). One way to split the table is to have a table for members and a table for dogs. We can draw a diagram of this (called an E-R diagram, or Entity-Relationship diagram). ‘Entity’ is just a name for a table, so this diagram shows the relationship between entities, or tables. In the diagram below, we have a ‘one-to-many relationship’.
It is a very good idea to put some real or made-up records into the tables you have identified as part of your E-R analysis. It really helps you to ‘visualise’ the problem. Note that when you split up a table into two or more smaller tables, the first thing you have to do is to check that each of the tables you have got has a primary key, a field whose value for every single record (i.e. every single row) will be unique - no other record will have the same value. The members’ table has such a field (ID) but the dogs’ table doesn’t have a suitable one. One simple solution (but not the only one) is to add an ID attribute to the dogs’ table and make this the primary key. We will call this new added field ‘DogID’.
The second thing we need to do is to check that each record in one table is related to a record in the other table. We can do this by using a ‘foreign key’; we copy the primary key from the one side (members) and put it in the many side (dogs). In other words, we copy the relevant ID from the member’s table and put in the relevant record for each dog. We now can view our improved database design:
Don’t worry if you are still a little confused! E-R diagrams can be confusing at first. There is a much more detailed explanation of E-R diagrams given in Section 2.
How is the relational database an improvement over the flat file design?
-
- Each member’s details are now only stored once.
- Each dog is now identified by their own unique identity number.
- A new member now has their details entered into only one record, not multiple records.
- Changes to records need only be made in one place.
- Details that are to be deleted only need to be deleted from one record.
Can we improve the design even further?
We have solved some problems by splitting the original flat file into two related tables. We haven't solved all the problems, however. We still have to record breed details over and over again and we can't record a new breed in our database unless there is a real dog of that breed actually owned by someone! We could try splitting up the dogs’ table into two, like this:
Reading all of the relationships, we now have:
-
- Each member can own many dogs.
- Each dog can be owned by only one owner.
- Each dog can be only one particular breed. (This is a dog club for pedigree dogs only!)
- Each breed can appear in many different dog records.
As before, we can add some records to the tables to make ‘seeing’ what we have designed a little easier.
How is this relational database an improvement over the flat file design?
Not only can we now do the following:
-
- Each member’s details are now only stored once.
- Each dog is now identified by their own unique identity number.
- A new member now has their details entered into only one record, not multiple records.
- Changes to records need only be made in one place.
- Details that are to be deleted only need to be deleted from one record.
But also:
-
- We only need to store the details of each breed once.
- If we add, delete or amend a record, we only have to make the change in one place.
- We can add a new breed to our database, even if a member has not acquired a dog of this breed yet.
Summary
-
- What we have seen is that flat files have their uses for small databases.
- As the number of records in a flat file increases, however, jobs that used to be simple and quick start taking a lot longer.
- There comes a time when it is much better to consider breaking up one big file to produce smaller tables that are related.
- These types of databases are known as ‘relational databases’.
- A useful technique to help the designer achieve this is Entity-Relationship modelling.
- We talk about producing an E-R diagram for a particular database.
- E-R diagrams show a database designer what tables they need in a particular database and how those tables are related to each other.
- E-R diagrams do not in themselves show the designer what fields need to go in each table.
- (The designer will produce another document - a Data Dictionary - to record this information.)
- As with any new computerised system, an organisation needs to think carefully before it goes ahead and installs a computerised database.