Flat files and relational databases
A flat file database keeps all the data in one table. The same details get stored again and again:
| OrderID | CustomerName | CustomerAddress | Product |
|---|---|---|---|
| 1 | Ann Jones | 3 High St | Keyboard |
| 2 | Ann Jones | 3 High St | Mouse |
| 3 | Ann Jones | 3 Hihg St | Monitor |
- Data redundancy: Ann's address is stored three times, wasting space.
- Data inconsistency: one copy has been mistyped, so which is right? Changing her address means changing every copy.
A relational database splits the data into linked tables (relations). Each fact is stored once, and tables are linked by keys.
Customer(CustomerID*, Name, Address)
Order(OrderID*, CustomerID, Product)
| Term | Means |
|---|---|
| Table (relation) | Data about one kind of thing (entity) |
| Record (row, tuple) | One instance, e.g. one customer |
| Field (column, attribute) | One item of data about each record |
Benefits: less redundancy, so less wasted space and fewer inconsistencies; data is updated in one place; data integrity can be enforced; flexible queries can combine tables; different users can be given different views; it scales to large amounts of data.
Drawbacks: more complex to design and set up, needs a DBMS and skilled staff, and queries joining many tables can be slower than reading one flat file.