2.2 How Databases Are Organised: Tables, Records, Fields

Every database, no matter how large or small, is built from exactly three structural building blocks: tables, records, and fields. This section covers ICDL skill set 1.1 (item 1.1.3) and skill set 1.2 (items 1.2.1–1.2.3) — the rules that keep a database's structure clean, searchable, and free of unnecessary duplication.

The Three Building Blocks

A database is organised as a set of tables. Each table is organised into records, and each record is organised into fields. This hierarchy — database → tables → records → fields — is the single most important structural fact in the entire syllabus, and it underpins everything from data entry to queries to reports.

• A table stores all the data about one subject — for example, a Customers table, a Products table, or an Orders table.
• A record is one row within a table, and it represents one single instance of that table's subject — one specific customer, one specific product, one specific order.
• A field is one column within a table, and it represents one single piece of data describing that instance — a customer's first name, a product's price, an order's date.

Rule 1: One Subject per Table (1.2.1)

A well-designed table should describe only one subject. Mixing unrelated subjects into a single table — for example, storing both customer details and order details in one table — quickly leads to repeated data (the same customer's name and address copied onto every order row) and makes the data harder to update accurately. The fix is to split the data into separate, subject-specific tables: a Customers table holding one row per customer, and a separate Orders table holding one row per order, linked together through a relationship (covered in section 2.4).

Rule 2: One Instance per Record (1.2.2)

Within a table, each record must represent exactly one occurrence of the subject. In a Customers table, one record is one customer — not a household, not a group of customers, and not partial information about two different customers combined. Keeping one instance per record is what allows you to search, sort, edit, or delete an individual customer without affecting any other customer's data.

Rule 3: One Data Element per Field (1.2.3)

Each field should store a single, indivisible piece of data — this is often called atomicity. Rather than one large field called FullName, a well-structured table uses separate fields for FirstName and LastName. Rather than one field called Address containing a street, city, and postal code jumbled together, a well-structured table separates those into individual fields such as Street, City, and PostalCode.

Splitting data this finely might feel unnecessary at first, but it has real, practical benefits:

• You can sort records by last name alone, or by city alone, instead of sorting an entire combined string.
• You can search or filter for just the postal code, without needing to parse a longer field.
• You avoid inconsistent formatting, since each field can be given its own data type and validation rule (covered in the next section).

Putting It Together: A Worked Example

Imagine a simple Customers table:

This single table demonstrates all three rules at once: it covers one subject (customers), each row is one record representing one specific customer, and each column is one field holding one indivisible piece of information about that customer.

Why This Structure Matters

Following these three organising rules is what makes the rest of the module possible. Clean, atomic fields make searching and sorting reliable (section 5.1). Well-separated, single-subject tables are what you connect together with relationships to avoid retyping the same data twice (section 2.4). And consistent record structure is what allows forms and reports to display and summarise data correctly later in the syllabus (chapters 6 and 7). Get the table/record/field structure right at the start, and everything downstream in the ICDL Using Databases module becomes far easier.

Naming Tables and Fields Clearly

Good structure goes hand in hand with good naming. Tables are usually named after the plural of the subject they store (Customers, Products, Orders), while fields are named after the single piece of data they hold (FirstName, UnitPrice, OrderDate). Clear, consistent names make it far easier to build accurate queries, forms, and reports later — a query that joins Customers to Orders is self-explanatory, while vague names like Table1 or Data2 force you to open the table just to remember what it contains. Consistent naming is not a syllabus item on its own, but it directly supports the one-subject, one-instance, one-element rules covered above.

For the Exam

Expect exam items that describe a poorly designed table — for example, one where a full name or a combined address is stored in a single field, or where customer and order data are mixed into one table — and ask you to identify which organisational rule has been broken. Keep the hierarchy firmly in mind: database → table (one subject) → record (one instance) → field (one data element).