3. Limitations of flat file

For all the advantages of a flat file. There are some limitations that a relational database is able to overcome.

Consider flat file database example below

Issues

1. Potential duplication. With perhaps thousands of records in a file, it can be a very tedious process to spot duplicated records. Especially if more than one person is maintaining the table.

2. Non-unique records. Notice that Mr & Mrs Jones have identical ID's '2'. There is nothing in a flat file system to stop this. But it is a very poor idea to have identical ID's in a database.

3. Harder to update. Suppose this table now needs to also store their work details - this will result in multiple records for each person. Again, this is fine - but suppose Sandra Jones now wanted to be known as 'Sandra Thompson'? Now multiple records need to be updated.

4. Inherently inefficient. What if an email field needs to be added?. If there are tens of thousands of records, there may be many people having no email address, but every record in a flat file database has to have the same fields, whether they are used or not.

5. Harder to change data format. Suppose the telephone numbers now have to have a dash between the area code and the rest of the number, like this 0223-44033. Adding that extra dash over tens of thousands of records would be a significant task in a flat file database.

6. Poor at complex queries. Flat files are excellent for simple filtering. For example, show all records where the field 'City' contains Hull. But for anything a bit more complicated, a flat file becomes very difficult to use. For example, find all records whose post code contains '23'.

7. Almost no security. You can protect a flat file database using a password for opening it. But once it is open, that person can usually see all fields. This is often not a good thing, for example there may be a confidential field containing their salary that only some people should be able to see.

For all these reasons, a relational database may be a better option..