A single flat-file table is useful for recording a limited amount of data. But a large flat-file database can be inefficient as it takes up more space and memory than a relational database. It also requires new data to be added every time you enter a new record, whereas a relational database does not. Finally, data redundancy – where data is partially duplicated across records – can occur in flat-file tables, and can more easily be avoided in relational databases.
Therefore, if you have a large set of data about many different entities, it is more efficient to create separate tables and connect them with relationships.
Relational databases allow data to be stored in a clear, organised manner across multiple tables. Links, known as relationships, are formed to allow the data to be shared across the tables.
For instance, a retail company might have different tables for the following information:
Product details could be complicated, eg if the company sold books there may be several categories within books, including author name, title, genre, physical size and many other details. Storing all this information in one flat-file table would create a very large table.
Normalisation is the process of analysing how to make databases more efficient by using separate tables to reduce redundant data. When a database is normalised, data is broken down into smaller tables and relationships are used to link them.