Creating Database Tables with Proper Structure

Master the art of designing efficient database tables - a fundamental CSEC IT skill!

1

The Design Phase: Planning Before Building

The Concept of "Design View": This is where we define the table structure (the "blueprint") versus "Datasheet View" (where we enter data). Design View is where database professionals spend most of their time planning.

Defining the Entity: Before creating a table, you must clearly identify what it represents. Is this a table for 'Library Books' or 'Book Loans'? Each table should represent one type of entity.

The Blueprint Planner

You're designing a database for a School Sports Day. List 5 pieces of information and decide if they belong in one table or two separate tables!

Information Needed for Sports Day:

Question: Should all this information go in one table, or should it be split into two tables (Students and Events)?

One Table

"SportsDay_Results"

Two Tables

"Students" & "Events"

2

Setting Field Names & Descriptions (Objective 8)

Naming Rules

  • Avoid spaces: Use underscores (Date_of_Birth) or CamelCase (DateOfBirth)
  • Keep it descriptive but short: "StuFirstName" is better than just "Name" or "Student's First Name Here"
  • Start with a letter: Not a number or symbol
  • No special characters: Avoid !, @, #, $, etc.
  • Be consistent: Use the same naming convention throughout your database

The "Description" Property

Always add a description to explain the field's purpose. This helps other database users and even yourself when you return to the database months later!

Naming Police

Click on each "bad" field name to see the corrected version according to database standards!

Student's Age!
1st_Name
X
Date of Birth
Email@Address
Phone#
3

The Anchor: Assigning the Primary Key

The Rule of Uniqueness

Every table must have a Primary Key (PK) - a field that uniquely identifies each record. No two records can have the same primary key value.

Auto-Number vs. Manual Entry

  • Auto-Number: The computer automatically generates unique numbers (1, 2, 3...). Best for new databases where no existing ID system exists.
  • Manual Entry: Using an existing unique code (e.g., Social Security Number, Student ID, VIN for cars). Use when records already have a unique identifier.
Spot the Key

Which field would make the best Primary Key for a "Students" table? Click to select!

👤

Student Name

Could have duplicates

🏠

Address

Multiple students could live at same address

🆔

Student ID

Unique for each student

⚧️

Gender

Not unique - many students share same gender

4

Defining Field Properties (Objective 9)

Key Field Properties

  • Field Size: Limit characters to optimize storage (State Code: 2 chars, Last Name: 50 chars)
  • Format: Control how data displays (Dates: DD/MM/YYYY, Currency: $0.00)
  • Default Values: Pre-populate fields with common values (Country: "Jamaica")
  • Required vs. Optional: Force entry for critical fields, allow blanks for optional ones
  • Input Mask: Guide users on format (Phone: (###) ###-####)
The Storage Squeezer

Drag the slider to see how field size affects disk space usage. Watch the "storage disk" fill up!

0%
Empty Database
255 characters

Field: Student_Comments

Database Wisdom: Setting appropriate field sizes saves storage space and improves database performance!

5

Data Validation Rules (Objective 7)

The "Gatekeeper" Logic

Validation rules prevent "Garbage In, Garbage Out" (GIGO) by checking data before it enters the database.

Common Validation Rules

  • Range Check: >10 AND <20 (Value must be between 10 and 20)
  • Presence Check: IS NOT NULL (Field cannot be empty)
  • Type Check: LIKE "???###" (Format must match pattern)
  • List Check: IN ("A","B","C") (Value must be from a list)

Validation Text

This is the error message users see when they violate a rule. Make it helpful! Example: "Please enter a score between 0 and 100."

The Rule Builder

Build a validation rule to ensure test scores are valid. Then test it with different values!

Validation Rule:

Test_Score <= 100

Validation Text:

Score must be 100 or less

Test Your Rule:

Enter a value to see if it passes validation:

6

Practical Sandbox: Build-a-Table

Guided Simulation: Follow this step-by-step interactive wizard to build a "Music Playlist" table. Practice what you've learned!

Build-a-Table Wizard

Complete all 4 steps to build your Music Playlist table correctly!

1
2
3
4

Step 1: Name Your Table

Choose a proper name for your music playlist table:

Hint: Use PascalCase or underscores, no spaces!

Step 2: Add 3 Fields

Create fields for your music playlist table:

Step 3: Select Primary Key

Which field should be the Primary Key? (Remember: Unique, Never Null)

Song_ID

AutoNumber field

Song_Title

Text field

Artist

Text field

Step 4: Set One Validation Rule

Add a validation rule to the "Song_Title" field:

Field Name Data Type Size Primary Key Song_ID AutoNumber - Yes Song_Title Text 100 No Artist Text 50 No
7

CSEC Exam Prep: The "Identify the Error" Challenge

Case Study: Poorly Designed Table

Below is a screenshot of a poorly designed "Library Members" table. Find and identify all 5 errors!

Identify the Error Challenge

Click on each error in the table below, then select the corresponding error type from the list!

Member ID First Name Last Name Date of Birth Phone Number Email Address
1001 Maria Rodriguez 15/03/2005 (876) 555-0123 maria@email.com
1002 David Chen 22/07/2004 (876) 555-0124 david@email.com
1001 Aisha Johnson 10/11/2006 (876) 555-0125 aisha@email.com
1004 Michael Brown 05/12/2003 8765550126 michael@email.com
1005 Sophia Williams 18/09/2005 (876) 555-0127 sophia@email.com

Error Types to Identify:

Duplicate Primary Key
Spaces in Field Names
Inconsistent Phone Format
Missing Required Field
Wrong Data Type

Error Explanation:

How to Fix:

CSEC I.T SBA: Database- Creating Tables and Relationships (Part 1)
Scroll to Top