← VCE Spreadsheets, SQL & Visualisation

Designing a table structure

Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 43, 7-8 Digital Technologies: Acquiring, storing and validating data — Module 22, IGCSE Computer Science 0478 — 9 Databases — Module 11, ACARA Digital Technologies Years 7–8 — Structured Data and Queries — Module 18, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 18, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 56, WA Computer Science ATAR Year 11 — Data Management — Module 36

Understanding Designing a table structure in VCE Spreadsheets, SQL & Visualisation

What it does: Two fields for two facts follows the one fact per field rule, so each name part can be sorted or searched on its own. Planning a table means choosing a clear field name and the right data type, such as Date for a birth date.

Common mistakes: This field holds two facts (first name and last name). Table design uses one fact per field, so it should be split into FirstName - Text and LastName - Text. Age is a number you may want to compare or calculate with, so its data type should be Number, not Text.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Designing a table structure to a class →

This module's key terms are also in a printable crossword (Modules 35–44) and word search (Modules 35–54).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A student wants to store a person's name in a database table. Which design follows 'one fact per field'?
Two fields: FirstName and LastNameOne field: FullName holding 'Mia Chen'
Matches
Name (Text) = "Mia Chen"
↓
Two facts are crammed into one field. First name and last name should be separate fields, FirstName and LastName.
Applying
Meanings
Term
table design
Meaning
Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.
Practice
Table: Students
Testing
Gaps
Field: FirstName, Type:
Check Answer
Spots
1Table: Students
2FirstName and LastName - Text

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Abyssal Dragon' badge — Deep Sea Legend, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A student wants to store a person's name in a database table. Which design follows 'one fact per field'?
A. Two fields: FirstName and LastName ✓
B. One field: FullName holding 'Mia Chen'
Each field should hold one fact. Splitting the name into FirstName and LastName makes it easy to sort by last name or to greet someone by first name.
2. Which is the better field name for a table about school pets?
A. PetName ✓
B. stuff
A clear field name tells you exactly what data is stored. 'stuff' does not describe the data at all.
3. You are planning a field called Age that will store whole numbers like 12 and 13. Which data type should you choose?
A. Number (integer) ✓
B. Date
Choosing the type for each field matches the data. Whole numbers like 12 belong in a number field, not a date field.
4. A design table lists each field with its data type. Which row is correct for a field storing a student's birthday?
A. DateOfBirth | Yes/No
B. DateOfBirth | Date ✓
A design table pairs each field name with a data type. A birthday is a date, so the type should be Date.
5. Which field design breaks the rule 'one fact per field'?
A. Phone: '0412 345 678'
B. Contact: 'Sam, 0412 345 678' ✓
The Contact field squashes a name and a phone number together. Each fact should have its own field, such as FirstName and Phone.

Match the Pairs

FirstName (Text), LastName (Text) → Two separate fields, each holding one fact about a person
Age (Number) → A field named Age that only stores numbers
Each field should hold only one fact, so a full name is split into two fields → FirstName | LastName
A design table lists the field name and the data type for each field → Field: BirthDate, Type: Date
Name (Text) = "Mia Chen" → Two facts are crammed into one field. First name and last name should be separate fields, FirstName and LastName.
Field: Height, Type: Text → The data type does not suit the data. Height is measured, so its type should be Number, not Text.

Key Term

table design
Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.

Fill in the Blanks

1. Field: FirstName, Type: Text
Hint: Type the data type that suits a person's first name, which is made of letters.
2. Field: LastName, Type: Text
Hint: Type the field name for the family name, written as one word and kept separate from the first name.
3. Field: Age, Type: Number
Hint: Type the data type that suits an age, which you could do maths with.
4. Field: BirthDate, Type: Date
Hint: Type the data type that suits a day, month and year.
5. Table: Students Field: FirstName, Type: Text Field: Score, Type: Number
Hint: Type the label word that comes before each field's data type in the design table.

Spot the Error

Question 1
A Table: Students
B FirstName and LastName - Text
C LastName - Text
D Age - Number
Line B — This field holds two facts (first name and last name). Table design uses one fact per field, so it should be split into FirstName - Text and LastName - Text.
Question 2
A Table: Pets
B PetID - Number
C Name - Text
D Age - Text
Line D — Age is a number you may want to compare or calculate with, so its data type should be Number, not Text.
Question 3
A Title, Author - Text
B Author - Text
C Pages - Number
Line A — This field squashes two facts, the title and the author, into one. Each fact needs its own field: Title - Text and Author - Text.
Question 4
A Table: Games
B Score - Yes/No
C Played - Date
D Won - Yes/No
Line B — A score is a count that can be added up or compared, so it needs the Number type. Yes/No only suits true or false facts like Won.
Question 5
A Table: Movies
B Title - Text
C Rating - Number
D Stuff - Date
Line D — The field name is vague. A clear field name such as ReleaseDate tells everyone exactly what fact is stored.

Student with a class code? Join your class →