Data Design Structured data is stored in tables.

These tables can either be stored as a flat file or as a relational table.

Flat file is data that is stored in a single table spreadsheet such as Excel can only store data as a flat file. They require very little design process and they allow simple searching and sorting of data.

However, they do have unnecessary repetition of data, which is known as data redundancy or duplication and they cannot handle complex queries.

So here we have an example of a flat file.

So we can see that each time a customer makes a transaction there is repetition of some of that information. So their first name their surname their date of birth are repeated. Every single time a transition is made.

In a relational database that is done very differently in a relational database. The data is split into two separate tables.

So for example, we can see here that the customer data such as their name, their job, their gender, their date of birth is held separately and the customer ID is the only value that is used when a transaction is made in the first table. The customer ID is a unique identifier, no two customers will use the same ID.

If we want to create a visual or do some analysis that requires data from both tables. So for example, what is the total amount of transactions for all meals then we will have to relate or join those two tables together, but the relational structure means that we're not repeating information.

So even though Ian Roberts might make hundreds of transactions, the only piece of information that's stored about Ian each time, he makes a transaction is his customer ID In order to relate these two tables together, we need to create a join and there are various types of joins. The most common one is a one-to-many relationship in a one-to-many relationship. The two tables must have a field in common.

Each value in one of those tables is used one time only.

In the second table that same value can be repeated many times.

If we look at our example again, we'll see here a typical one to many relationship. So in the bottom table, we have a customer ID for Ian Roberts and in the top table every transaction that's made uses that same customer ID. So customer ID is unique in the bottom table. Each customer has a different ID. The customer ID in the top table can be repeated many times.