Designing Simple Database Tables

Active learning works well for this topic because designing tables requires students to make concrete decisions about data, not just absorb concepts. When students sketch tables themselves or critique peers’ designs, they see immediately how structure affects usability, making abstract ideas like primary keys tangible and memorable.

• JC 1
• Computing
• 4 activities
• 20 min–45 min

Learning Objectives

1. Design a simple database table to store information about a specific entity, such as books in a library, including appropriate fields.
2. Identify and justify the selection of a primary key for a given database table to ensure unique record identification.
3. Analyze a set of data requirements and classify them into logical fields suitable for a single database table.
4. Critique the design of a simple database table for potential redundancy or missing information.

Case Study Analysis

⏱ 30 min·Pairs

Pair Design Challenge: Student Roster Table

Pairs list fields for a class roster, such as ID, name, form, and email. They select and justify a primary key, then draw the table structure. Pairs swap designs to spot improvements.

Prepare & details

How do we decide what information belongs in a single database table?

Facilitation Tip: During the Pair Design Challenge, ask pairs to swap tables and explain their design choices aloud to uncover hidden assumptions.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Case Study Analysis

⏱ 45 min·Small Groups

Small Group Scenario: Event Registration Tables

Groups receive an event scenario and identify fields like attendee ID, name, and dietary needs. They create a table with primary key and discuss why certain data stays in one table. Groups present to class for feedback.

Prepare & details

What is a primary key, and why is it important for a database table?

Facilitation Tip: For the Small Group Scenario, give each group a different event type (e.g., sports day, art competition) to highlight how fields vary by context.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Case Study Analysis

⏱ 20 min·Whole Class

Whole Class Refinement: Library Book Table

Project a partial book table on screen. Class suggests fields like ISBN, title, and author, votes on primary key. Teacher guides additions and revisions collaboratively.

Prepare & details

Design a simple table to store information about students in a class, including their unique ID.

Facilitation Tip: When refining the Library Book Table as a whole class, deliberately introduce a bloated version first, then guide students to split it into two related tables.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Case Study Analysis

⏱ 25 min·Individual

Individual Prototype: CCA Member Table

Students independently design a table for CCA members with fields like member ID and role. They add sample data and identify their primary key choice.

Prepare & details

How do we decide what information belongs in a single database table?

Facilitation Tip: For the Individual Prototype, provide a partially completed template so students focus on field selection and key design rather than formatting.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Teaching This Topic

Teachers often start with concrete examples students recognize, like class rosters or club memberships, before introducing theory. Avoid rushing to normalization rules; instead, let students experience the pain of messy data firsthand during redesign activities. Research shows this approach builds deeper understanding than lecturing upfront, as students confront the consequences of poor design choices directly.

What to Expect

Successful learning looks like students confidently selecting fields, justifying their primary key choices, and recognizing when to split data across tables. By the end, they should be able to explain why a well-structured table avoids redundancy and speeds up searches, using their own examples.

Watch Out for These Misconceptions

Common Misconception

During the Pair Design Challenge, watch for students grouping all club data (e.g., member names, event dates, fees) into one table.

What to Teach Instead

After pairs present their designs, ask the class to identify update anomalies: What happens when a member’s contact number changes or an event date shifts? Guide them to split tables into Members, Events, and Registrations, linking with keys.

Common Misconception

During Small Group Scenario, watch for groups selecting names as primary keys for event registrations.

What to Teach Instead

Provide sample data with duplicate names and ask groups to test their key choices by attempting to insert new records. This reveals why auto-generated IDs work better than names for uniqueness.

Common Misconception

During Whole Class Refinement, watch for students storing multiple values in a single field, like a list of genres for each book.

What to Teach Instead

Ask students to attempt a query like 'Find all books with the genre Mystery.' When they realize the query fails, demonstrate how splitting genres into a separate table with a foreign key solves the problem.

Assessment Ideas

Quick Check

After the Pair Design Challenge, present students with a list of data points for a school club roster (e.g., Member Name, Membership ID, Club Name, Contact Number, Join Date). Ask them to identify which should be fields and suggest a primary key, explaining their choice in a sentence.

Exit Ticket

During the Small Group Scenario, provide students with a partially completed table for a sports event registration. Ask them to list three additional fields they would add and justify their primary key choice based on the data.

Discussion Prompt

After the Whole Class Refinement, pose the question: 'Imagine your library table now includes both books and borrowers. What problems could arise? How would you redesign it?' Facilitate a class discussion on field selection and the role of primary and foreign keys in solving these issues.

Extensions & Scaffolding

• Challenge: Ask students to design two related tables (e.g., Students and Enrollments) with a foreign key, then write three example queries to demonstrate how the tables relate.
• Scaffolding: Provide a list of fields with some marked as optional or redundant, and ask students to identify which to include or split.
• Deeper exploration: Have students research how databases like SQLite enforce data types and constraints, then design a table that includes these constraints.

Key Vocabulary

Field	A single piece of information within a database record, such as 'Student Name' or 'Date of Birth'.
Record	A complete set of information about one item in a database table, consisting of all its fields. For example, all information about one student.
Table	A collection of related records, organized into rows (records) and columns (fields).
Primary Key	A field or set of fields that uniquely identifies each record in a database table, preventing duplicates and allowing for efficient data retrieval.