Relational Database Design: Tables and Fields

Active learning works for relational database design because students need to experience constraints firsthand. When they design tables themselves, they confront why data types matter, how redundancy harms efficiency, and what happens when rules are broken.

• Secondary 4
• Computing
• 4 activities
• 20 min–35 min

Learning Objectives

1. Analyze the structure of a relational database by identifying distinct tables and their constituent fields.
2. Differentiate between common data types (e.g., INTEGER, TEXT, DATE, BOOLEAN) and justify their appropriate use for specific fields.
3. Design a simple table schema, including field names and data types, for a given real-world entity.
4. Explain how the choice of data types impacts data integrity and storage efficiency within a table.

Concept Mapping

⏱ 30 min·Pairs

Pair Design Challenge: Library Schema

Pairs read a school library scenario and list entities. They assign fields with data types, justify choices in a shared document. Pairs then swap schemas for peer review on type suitability.

Prepare & details

Analyze how tables and fields organize data in a structured manner.

Facilitation Tip: During the Pair Design Challenge, circulate to ask guiding questions like 'What would happen if two books shared the same ISBN?' to push students beyond superficial designs.

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Concept Mapping

⏱ 25 min·Small Groups

Small Group Sort: Data Type Relay

Groups receive sample data items like emails or prices. They race to assign correct fields and types to a template table, then explain selections to the class. Discuss common errors as a wrap-up.

Prepare & details

Differentiate between various data types and their appropriate use in database fields.

Facilitation Tip: In the Data Type Relay, assign each group a unique set of sample data to ensure varied practice and discussion across the class.

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Concept Mapping

⏱ 35 min·Whole Class

Whole Class Build: Event Database

Project a blank table on the board for school events. Class suggests fields and types via sticky notes or shouts, vote on best options. Teacher inputs into a demo database to show live validation.

Prepare & details

Construct a simple table schema for a real-world entity.

Facilitation Tip: For the Whole Class Build, provide a scaffolded starter schema so students focus on relationships and constraints rather than starting from scratch.

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Concept Mapping

⏱ 20 min·Individual

Individual Validation: Schema Audit

Students get printed flawed schemas with wrong types. They identify issues, correct fields, and note impacts on queries. Share one fix with a partner for confirmation.

Prepare & details

Analyze how tables and fields organize data in a structured manner.

Facilitation Tip: In the Schema Audit, model how to critique one another’s work with specific feedback before students exchange papers.

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Teaching This Topic

Teach this topic by moving from concrete examples to abstract principles. Start with a flawed spreadsheet to show the chaos of unstructured data, then transition to a simple database to demonstrate how types and keys restore order. Avoid overwhelming students with theory upfront; let them discover the need for rules through their own design failures.

What to Expect

Successful learning looks like students identifying appropriate fields and data types for real-world scenarios, explaining their choices, and recognizing when a design needs refinement. They should also articulate why structure supports function in databases.

Watch Out for These Misconceptions

Common Misconception

During the Pair Design Challenge, watch for students treating tables like spreadsheets by replicating data across rows or ignoring constraints on input.

What to Teach Instead

Redirect groups by asking them to compare inserting invalid data (e.g., a text title into an INTEGER ISBN field) in both a spreadsheet and their database schema. Discuss which system catches the error and why.

Common Misconception

During the Data Type Relay, watch for students assuming any data type can store any value without consequences.

What to Teach Instead

Have each group attempt to calculate the average price of items stored as TEXT in their relay task. When the calculation fails, prompt them to propose a better type and explain the trade-offs.

Common Misconception

During the Whole Class Build, watch for students adding excessive fields to tables under the belief that more is always better.

What to Teach Instead

Pause the activity and ask groups to justify each field in their schema. Challenge them to remove one field and explain how the design remains functional without it. Use examples like 'phone_number' for a 'Books' table to highlight relevance.

Assessment Ideas

Exit Ticket

After the Pair Design Challenge, provide each student with a new scenario (e.g., 'Design a table for library members'). Ask them to list 4 fields, specify a data type for each, and identify a primary key. Collect and review these to check understanding of table structure and data types.

Quick Check

During the Data Type Relay, display a sample schema with incorrect types (e.g., 'Quantity' as TEXT). Ask students to identify the error and suggest the correct type, then discuss answers as a class to reinforce precision.

Discussion Prompt

After the Whole Class Build, pose the question: 'What would go wrong if we stored all data as TEXT?' Facilitate a class discussion where students share examples of errors or inefficiencies, connecting their experiences to real-world applications.

Extensions & Scaffolding

• Challenge: Ask students to design a second table for the library schema that relates to 'Books' and write three example queries that join the tables.
• Scaffolding: Provide a list of possible fields and data types for the Pair Design Challenge to reduce cognitive load for struggling students.
• Deeper exploration: Have students research how NULL values affect query results and modify their library schema to include a field where NULL is meaningful.

Key Vocabulary

Table	A collection of related data entries organized in rows and columns. Each table represents a specific entity, like 'Students' or 'Courses'.
Field	A column within a table that represents a specific attribute or characteristic of the entity. For example, 'StudentID' or 'FirstName' are fields in a 'Students' table.
Data Type	A classification that specifies the kind of data a field can hold, such as text, numbers, dates, or true/false values. Data types ensure data consistency and validity.
Primary Key	A field or set of fields that uniquely identifies each record (row) in a table. It ensures that no two records are identical.