4.2 Table Design: Fields, Data Types, Properties, Validation, Keys, Indexes

Table design is the largest single skill set on the ICDL Using Databases exam, covering Task Items 3.2.1 through 3.2.8. Everything that happens later — queries, forms, reports — depends on the table being designed correctly at the start. This section walks through creating a table, choosing data types, setting field properties, and applying keys and indexes.

Creating and Naming a Table

A new table is created in Design view, where you list each field name down the left column and choose its data type in the column beside it. Field names should be short, descriptive, and free of spaces where possible (many database styles use names like CustomerID or DateOfBirth). Once the fields are defined, saving the table for the first time prompts you to give the table itself a name, separate from any of its field names.

Assigning a Data Type to Each Field

Every field must be assigned exactly one data type, which controls what kind of value can be stored and how Access processes it (sorting, calculating, validating). The exam expects familiarity with these common types:

Choosing the correct type matters beyond storage: a Number or Currency field can be summed and sorted numerically, while a Text field storing the same digits would sort alphabetically (so "10" would sort before "9"). A Yes/No field displays as a checkbox rather than free text, which prevents inconsistent entries like "yes", "Y", and "true" all meaning the same thing in different rows.

Field Properties: Refining a Data Type

Once a data type is chosen, field properties let you refine exactly how that type behaves — they do not change the type itself. Key properties tested on this exam:

• Field Size — for a Text field, the maximum number of characters allowed; for a Number field, the numeric range and precision (e.g., Integer vs Long Integer).
• Format — controls how a value displays, such as a Number field showing two decimal places, or a Date/Time field displaying as dd/mm/yyyy versus d-mmm-yy.
• Default Value — a value that is automatically pre-filled in the field for every new record, saving repetitive typing when most records share the same value (for example, defaulting a "Country" field to "South Africa").
• Validation Rule — an expression that restricts which values are acceptable; for example, a rule requiring a Number field to be greater than zero rejects negative quantities at the point of entry.
• Required — a property that forces a field to always contain a value, preventing a record from being saved with that field left blank.

Exam distinction to remember: a data type is the broad category of value a field can hold (Text, Number, Date/Time, and so on); a field property is a finer setting layered on top of that type (its size, its display format, its default, its validation). Changing a property does not change the type — but changing the type itself can have consequences.

Consequences of Changing a Data Type or Property

Changing a field's data type or certain properties after data already exists can cause data loss or errors. For example, converting a Text field that contains "N/A" entries into a Number field will fail or blank out those non-numeric values, because Number fields cannot store text. Similarly, shrinking a Text field's Field Size from 50 to 10 characters truncates any existing values longer than 10 characters. Always consider existing data before changing a field's type or size on a table that is already in use.

Setting a Primary Key

A primary key is a field (or combination of fields) whose value uniquely identifies each record in the table. Access enforces two rules automatically on a primary key field: it cannot contain duplicate values, and it cannot be left blank. Setting a primary key is done in Design view by selecting the field and clicking the key icon on the ribbon; the field then displays a small key symbol beside its name. Many tables use an AutoNumber field as the primary key precisely because Access guarantees each generated value is unique.

Creating and Modifying an Index

An index speeds up searching and sorting on a field, in the same way an index at the back of a book speeds up finding a topic without reading every page. Indexes are created in Design view through a field's Indexed property, which offers a choice between:

• Yes (No Duplicates) — the field is indexed and every value must be unique (this is automatically true of a primary key).
• Yes (Duplicates OK) — the field is indexed for faster searching, but the same value may appear in multiple records (useful for a field like Surname or City that is searched often but is not unique).

A primary key is always indexed with no duplicates allowed, but a field does not need to be a primary key to be indexed — you can add an index to any field that is frequently used in searches or sort operations to improve query performance.

Adding a Field to an Existing Table and Adjusting Column Width

Two further practical skills round out this section: adding a new field to a table that already contains data, and adjusting how a table displays. A new field can be added at any time in Design view (or by using the "Click to Add" column in Datasheet view); existing records simply receive a blank value in the new field until it is filled in. Column width is purely a display setting — dragging the border between two column headers in Datasheet view widens or narrows how much of a field's content is visible on screen, without altering the underlying Field Size property or any stored data.