The Ins and Outs of SQL Database Design: A Beginner's Guide

Introduction

Databases are used extensively in daily life to store, manage, and retrieve structured information, powering the digital services we interact with constantly. They act as the backend infrastructure for online shopping, banking, social media, and streaming platforms, ensuring data is accurate, secure, and accessible.

The primary reason you should be concerned with database design is that it is crucial to the consistency, integrity, and accuracy of the data in a database.

Database design is the foundation for the complete implementation of the physical database you are building. This involves creating your tables, establishing table relationships and ensuring the integrity of data stored in your tables.

This article will focus on the use of SQL (PostgreSQL) to build a mock-up database for a fictitious school 'Greenwood Academy' and querying (retrieving) data from it as a new database administrator for the school using DBeaver to execute my queries. You can use open-source tools like DBeaver or pgAdmin to visually explore the database schema we are about to write.

Some of the basic terms I'll be using are:

• Database: A collection of organized data.
• Schema: A named storehouse within a database.
• Table: A structure that stores data in rows and columns.
• Row: A single entry (record) in a table.
• Column (field): An attribute of the data.
• Primary Key (PK): Uniquely identifies each record in a table.
• Foreign key (FK): is a column or set of columns in a database table that establishes a relationship with another table by referencing its primary key.
• Query: A request to access or modify data.
• Constraint: Rules applied to ensure data validity.

Building the database

As the new database administrator, your role revolves around designing the database structure (schema and tables), defining the data types for each column in your table ensuring data integrity and identifying the primary and foreign keys to build table relationships.
We will be working with:

• Schema: greenwood_academy
• Table 1: students
• Table 2: subjects
• Table 3: exam_results

SQL commands are mainly divided into five categories:

DDL (Data Definition Language)

It consists of commands that can be used to create or modify database structures such as tables and schemas.
Some of the DDL commands we will be working with are:

Command	Description	Syntax
CREATE	Creates database objects like schemas and tables	CREATE SCHEMA schema_name;
DROP	Deletes objects from the database	DROP TABLE table_name;
ALTER	Changes or modifies the structure of the database	ALTER TABLE table_name ADD COLUMN column_name data_type;
RENAME	Renames an object residing in your database	RENAME COLUMN column_name TO new_column_name

Creating the schema

CREATE SCHEMA IF NOT EXISTS greenwood_academy;

-- setting the search path
SET search_path TO greenwood_academy;

Run SET search_path TO greenwood_academy; at the start of your SQL session. This tells PostgreSQL where to look for your tables, allowing you to type students instead of greenwood_academy.students in every query!

Creating the tables

-- Creating the students table

CREATE TABLE students (
    student_id       INT PRIMARY KEY,
    first_name       VARCHAR(50) NOT NULL,
    last_name        VARCHAR(50) NOT NULL,
    gender           VARCHAR(1),
    date_of_birth    DATE,
    class            VARCHAR(10),
    city             VARCHAR(50)
);

The above code block creates a table students with columns student_id, first_name, last_name, gender, date_of_birth, class and city. It describes student_id as the PRIMARY KEY and it should only accept INT (whole numbers) as an entry, first_name and last_name as VARCHAR(50) (text fields of variable length, shouldn't exceed length of 50) and they shouldn't be empty, gender as a VARCHAR(1) (fixed length text field, only accepts one character as an entry), date_of_bith as a DATE datatype, and class and city as text fields with variable length ( shouldn't exceed 10 and 50 respectively).

-- Creating the subjects table
CREATE TABLE subjects (
    subject_id        INT PRIMARY KEY,
    subject_name      VARCHAR(100) NOT NULL UNIQUE,
    department        VARCHAR(50),
    teacher_name      VARCHAR(100),
    credits           INT
);

This creates a table named students with columns subject_id as the PRIMARY KEY, subject_name as a text field of variable length (doesn't exceed length of 100) with a constraint UNIQUE (no duplicates), department and teacher_name as text fields of variable length (shouldn't exceed 50 and 100 respectively), and credits as an INT (only accepts whole numbers).

-- Creating the exam_results table
CREATE TABLE exam_results (
    result_id   INT PRIMARY KEY,
    student_id  INT NOT NULL 
        REFERENCES greenwood_academy.students (student_id),
    subject_id  INT NOT NULL 
        REFERENCES greenwood_academy.subjects (subject_id),
    marks       INT NOT NULL,
    exam_date   DATE,
    grade       VARCHAR(2)
);

This creates a table named exam_results with columms result_id, student_id, subject_id, marks, exam_date and grade.
It describes result_id as the PRIMARY KEY (uniquely identifies each record in the exam_results table), marks as an INT and should never be empty, exam_date as a DATE field and grade as a text field of fixed length (2). It also defines two FOREIGN KEYs, student_id and subject_id which references the records in the students and subjects tables we created earlier respectively. This establishes the relationship between our tables.

Imagine a scenario where after creating the students table, the school realizes they forgot to include a phone number column, the column credits in the subjects table needs to be renamed to credit_hours and later on they realize they no longer need the phone_number column you added. As the database administrator for the school implement those changes.

-- Adding column phone_number to students
ALTER TABLE students 
ADD COLUMN phone_number VARCHAR(20);

-- Renaming credits to credit_hours in the subjects table
ALTER TABLE subjects 
RENAME COLUMN credits TO credit_hours;

-- Dropping the phone_number column in the students table
ALTER TABLE students 
DROP COLUMN phone_number;

Filling the Database (DML: INSERT, UPDATE, DELETE)

Now that we have the proper structure for our database we enter records to our tables using DML (Data Manipulation Language).
With DML you can insert new records, update existing ones and delete unwanted data from our database.
The commands we will be using are:

Command	Description	Syntax
INSERT	Inserting data into a table	INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
UPDATE	Updates the existing records in a table	UPDATE table_name SET column1 = value1 WHERE condition;
DELETE	Deletes records from a database table	DELETE FROM table_name WHERE condition;

Inserting records into our tables

-- inserting records into students
INSERT INTO students (
    student_id, 
    first_name, 
    last_name, 
    gender, 
    date_of_birth, 
    class, 
    city
) VALUES 
    (1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    (2, 'Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
    (3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
    (4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
    (5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru'),
    (6, 'Felix', 'Otieno', 'M', '2009-09-14', 'Form 2', 'Eldoret'),
    (7, 'Grace', 'Mwangi', 'F', '2008-01-22', 'Form 3', 'Nairobi'),
    (8, 'Hassan', 'Abdi', 'M', '2007-04-09', 'Form 4', 'Mombasa'),
    (9, 'Ivy', 'Chebet', 'F', '2009-12-01', 'Form 2', 'Nakuru'),
    (10, 'James', 'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');

-- Inserting records into subjects
INSERT INTO subjects (
    subject_id,
    subject_name,
    department,
    teacher_name,
    credit_hours
) VALUES 
    (1, 'Mathematics', 'Sciences', 'Mr. Njoroge', 4),
    (2, 'English', 'Languages', 'Ms. Adhiambo', 3),
    (3, 'Biology', 'Sciences', 'Ms. Otieno', 4),
    (4, 'History', 'Humanities', 'Mr. Waweru', 3),
    (5, 'Kiswahili', 'Languages', 'Ms. Nduta', 3),
    (6, 'Physics', 'Sciences', 'Mr. Kamande', 4),
    (7, 'Geography', 'Humanities', 'Ms. Chebet', 3),
    (8, 'Chemistry', 'Sciences', 'Ms. Muthoni', 4),
    (9, 'Computer Studies', 'Sciences', 'Mr. Oduya', 3),
    (10, 'Business Studies', 'Humanities', 'Ms. Wangari', 3);

-- Inserting records into exam_results
--------------------------------------
INSERT INTO exam_results (
    result_id,
    student_id,
    subject_id,
    marks,
    exam_date,
    grade
) VALUES 
    (1, 1, 1, 78, '2024-03-15', 'B'),
    (2, 1, 2, 85, '2024-03-16', 'A'),
    (3, 2, 1, 92, '2024-03-15', 'A'),
    (4, 2, 3, 55, '2024-03-17', 'C'),
    (5, 3, 2, 49, '2024-03-16', 'D'),
    (6, 3, 4, 71, '2024-03-18', 'B'),
    (7, 4, 1, 88, '2024-03-15', 'A'),
    (8, 4, 6, 63, '2024-03-19', 'C'),
    (9, 5, 5, 39, '2024-03-20', 'F'),
    (10, 6, 9, 95, '2024-03-21', 'A');

After inserting the data, run a SELECT query to confirm all records exist in each of the three tables.

-- Quick Sanity Check
SELECT * FROM students;
SELECT * FROM subjects;
SELECT * FROM exam_results;

Imagine a scenario where Esther Akinyi has moved from Nakuru to Nairobi (her student_id is 5), the marks for result_id 5 were entered incorrectly - the correct marks are 59, not 49, and the exam result with result_id 9 has been cancelled by the school. As the database administator make those those changes to ensure integrity of the data in the school's database.

-- updating Esther Akinyi's city from Nakuru to Nairobi
UPDATE students 
SET city = 'Nairobi'
WHERE student_id = 5;

-- updating marks for result_id 5 from 49 to 59
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

-- deleting records for result_id 9 from exam_results
DELETE FROM exam_results 
WHERE result_id = 9;

DELETE : The WHERE Clause is Critical: Always include a WHERE clause to specify which rows to delete; omitting it will delete all records in the table, leaving only the empty schema.

Querying the Data (DQL)

DQL (Data Query Language) is used to fetch data from the database. The main command is SELECT, which retrieves records based on the query. The output is returned as a result set (a temporary table) that can be viewed or used in applications.
The commands we will be using are:

Command	Description	Syntax
SELECT	It is used to retrieve data from the database	SELECT * FROM table_name;
FROM	Indicates the table from which to retrieve data	SELECT column1 FROM table_name;
WHERE	Filters the data in our tables	SELECT FROM column1 WHERE condition;

Note: DQL has only one command, SELECT. Other terms like FROM, WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT and LIMIT are clauses of SELECT, not separate commands.

Examples:

SELECT student_id, first_name, last_name
FROM students 
WHERE class = 'Form 4';

This query retrieves only the names of students with their student id from the students table for those in the 'Form 4' class.

SELECT subject_name, department
FROM subjects 
WHERE department = 'Sciences';

This query retrieves subjects and it's department only for subjects in the 'Sciences' department.

Conclusion

Designing a database is more like constructing a house: you wouldn't start by laying bricks without a solid blueprint. By using DDL (CREATE SCHEMA, CREATE TABLE, ALTER TABLE) to establish our greenwood_academy schema, we built a robust and logical structure. By implementing primary and foreign keys we established our data relationship pathways - connecting students, subjects, exam_results - maintaining data integrity.

Once the physical structure was ready, we used DML (INSERT, UPDATE, DELETE) to breathe life into our database with real records.

A well-designed database is the ultimate foundation for any backend application you build next. Spending time getting your schemas, tables, and relationships right up front saves you from massive refactoring headaches down the road.

Comments

[Luis Cruz]

I appreciate how you've outlined the process of creating a database schema for the Greenwood Academy example, specifically highlighting the importance of setting the search path with SET search_path TO greenwood_academy; to simplify queries. The use of CREATE TABLE statements, such as the one for the students table, effectively demonstrates how to define a table structure with primary keys and various data types. One consideration for further enhancing data integrity could be adding additional constraints, such as CHECK constraints to validate the format of the date_of_birth or ensuring the gender field only accepts specific values. How do you suggest handling potential data inconsistencies or invalid inputs in such a database design?

[phillip nzioka]

When building a schema for an educational institution like Greenwood Academy, laying down protective barriers directly inside the database schema ensures that no matter how flawed upstream application code might be, the database remains a clean, trusted source of truth.
To expand on the current design of our students table, several strategies can be implemented directly within our PostgreSQL scripts to validate inputs and prevent inconsistencies.

Robust CHECK constraints

CHECK constraints are the most direct way of evaluating raw data before it's committed. If the condition evaluates to false, the database rejects the insertion.
For the students you can prevent invalid inputs with the following:

ALTER TABLE greenwood_academy.students 
ADD CONSTRAINT check_student_gender 
  CHECK (gender IN ('M', 'F')),
ADD CONSTRAINT check_student_dob 
  CHECK (date_of_birth > '1990-01-01' AND date_of_birth <= CURRENT_DATE);

Rather than ensuring date_of_birth is a date, you can ensure it sits within a realistic window.

Foreign Key Cascading

When managing related entities such as exam_results table that references both the students and subjects tables, ensure cascading actions are specified so orphaned records aren't left behind if a student leaves the academy:

ALTER TABLE greenwood_academy.exam_results
  ADD CONSTRAINT fk_exam_student
  FOREIGN KEY (student_id) 
  REFERENCES greenwood_academy.students(student_id)
  ON DELETE CASCADE;

[leslie angu]

The article has a really good flow from the start when you introduced databases to the end where you queried the databases.