Start With The Requirement

A database design question normally provides a scenario such as a school storing examination entries or a shop storing product details. The wording tells you what data must be stored and what operations may be required later.

Read for nouns that describe data items and for verbs that indicate future use. If the system must find all products below a price, Price must be a separate numeric field. If it must count products that are available, Availability should be stored in a consistent field, commonly Boolean.

Do not add fields simply because they are common in real databases. Cambridge asks for a design from the given storage requirements, so each field should have a clear purpose in the scenario.

Identify What One Record Represents

The table must have a consistent entity. In a library book table, each record may represent one physical book or one book title, depending on the requirement. The choice affects the fields and primary key.

Ask the question: What does one row describe? Write the answer before listing fields. If the answer changes from row to row, the design is not consistent.

A single-table design should avoid storing repeated groups in one record. For example, Subject1, Subject2 and Subject3 may be tempting, but if the requirement only asks for one selected subject, use one Subject field. Do not invent complex structures outside the stated need.

Turn Required Data Into Fields

Each separately searched, compared, sorted, counted or totalled item should normally be stored in its own field. A product code and product name are different facts and therefore require different fields.

Choose field names that describe the data precisely. ProductID, ProductName, UnitPrice, StockQuantity and IsDiscontinued are clearer than Code, Name, Value, Number and Flag when the table is read without the original question.

Field names should be unique within the table. Two fields both called Date would be ambiguous; use OrderDate and DeliveryDate if both are required.

Add Data Types

Every field should be assigned one of the basic database data types required by Cambridge: text/alphanumeric, character, Boolean, integer, real or date/time. The type must suit the values and the intended operations.

An identifier may contain digits but still be text/alphanumeric if it includes letters, leading zeros or no arithmetic is performed. A price should normally be real because a fractional part may occur. A whole stock quantity is integer.

A single letter grade can use character, while a multi-character name uses text/alphanumeric. A yes/no status is best represented by Boolean rather than inconsistent text such as Yes, Y, true and 1.

Add A Primary Key And Validation

Choose a primary key that uniquely identifies every record. It must not contain duplicates and should not be blank. Codes designed for identification are often suitable. Names are usually unsuitable because different people or products can share the same name.

Validation rules should reflect the requirement. A mark may require a range check from 0 to 100. A product code may require a length or format check. A required name may use a presence check.

Validation is attached to the relevant field and is applied when data is entered. The rule should not reject valid values described by the scenario.

Complete Design Example

Checking The Design

A complete answer should allow a reader to create the table without guessing. Check that every requirement is represented, every field has a suitable type, one suitable primary key is identified, and validation is stated where appropriate.

Also check that the design remains within one table. Do not include foreign keys, relationships, normalisation or joins because they are not part of Topic 9.

Worked Examples

Examination Guidance

• Base every field on the scenario.
• State clearly what one record represents.
• Give both the field name and its data type.
• Identify the primary key explicitly.
• Include validation only when it is relevant and state the allowed condition.

Knowledge Check

1. What should be identified before fields are chosen?

2. Why separate data into fields?

3. What makes a field relevant?

4. What must a complete table definition include?

5. Should unrequested fields be added?