Create a database structure

A database stores organised data in one or more tables. Think of a table like a grid. Each column in the grid is called a 'field', which stores a single piece of information, like 'FirstName' or 'EmailAddress'. Each row in the grid is called a 'record', which is a complete set of information for one person or item, containing all the field data for that single entry. The entire grid of fields and records is called a 'table'.

When you create a field in a database table, you must assign it a 'data type'. This tells the database what kind of data to expect and how to handle it. Choosing the correct data type is crucial for data integrity and efficient processing. For example, you can't perform mathematical calculations on a field set to 'Text'. Common data types include:

• Text/Short Text: For names, addresses, and other text.
• Number/Integer: For whole numbers used in calculations (e.g., Quantity).
• Currency: For monetary values, automatically formatted with a currency symbol and two decimal places.
• Date/Time: For storing dates and times in a specific format.
• Boolean (Yes/No): For fields that can only have two values, such as True/False or Yes/No.

Every table in a database needs a 'primary key'. A primary key is a field (or a combination of fields) that contains a unique value for each record. This ensures that every record can be uniquely identified, just like a National Insurance number identifies a unique person. Two records in a table can never have the same primary key value. Furthermore, the primary key field cannot be left empty (it cannot be 'null'). Often, an 'AutoNumber' data type is used to automatically generate a new, unique number for each record added, making it a perfect primary key.

There are two main ways to structure a database. A 'flat-file' database stores all data in one single, large table. This is simple to set up for small amounts of data but quickly becomes problematic. The main issue is 'data redundancy' – the same information (like a customer's name and address) is repeated for every purchase they make. This wastes space and can lead to 'data inconsistency', where updating the address in one place but not another creates conflicting information. A 'relational' database solves this by splitting data into multiple, smaller tables based on themes (e.g., a 'Customers' table and an 'Orders' table). These tables are then linked, which reduces data redundancy and improves data integrity, making the database more efficient and reliable.

In a relational database, tables are linked together using keys. We know a 'primary key' uniquely identifies a record in its own table. A 'foreign key' is a field in one table that is a copy of the primary key from another table. This creates the link. For example, imagine a 'Students' table with a primary key `StudentID`, and a 'Locker_Allocations' table. To assign a locker to a student, we would add a `StudentID` field to the `Locker_Allocations` table. In this table, `StudentID` is the foreign key. It 'points' back to the original `StudentID` in the `Students` table, creating a relationship. This typically forms a 'one-to-many' relationship: one student (the 'one' side) can be linked to many entries in another table (e.g., one student could borrow many library books).