Table of Contents
Understanding Relational Database Design
Relational database design is the process of organizing data into tables and defining relationships between them. Good design ensures data integrity, reduces redundancy, and improves performance.
Core Components of Relational Design
1. Tables (Relations)
Tables are the foundation of relational databases. Each table represents an entity (like customers, products, or orders).
2. Attributes (Columns)
Each column represents a property of the entity. For example, a Customer table might have columns for name, email, and phone.
3. Tuples (Rows)
Each row represents a single instance of the entity.
Types of Keys
Primary Key
A unique identifier for each row in a table. Rules:
- Must be unique for each row
- Cannot be NULL
- Should not change over time
- Should be simple (preferably a single column)
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);
Foreign Key
A field that refers to the primary key of another table, creating relationships between tables.
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Composite Key
A primary key made up of multiple columns when no single column can uniquely identify a row.
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Types of Relationships
One-to-One (1:1)
Each record in Table A relates to exactly one record in Table B.
Example: Each employee has one employee profile.
One-to-Many (1:M)
Each record in Table A can relate to multiple records in Table B.
Example: One customer can have many orders.
Many-to-Many (M:M)
Records in Table A can relate to multiple records in Table B, and vice versa.
Example: Students can enroll in multiple courses, and courses can have multiple students.
-- Junction table for many-to-many relationship
CREATE TABLE student_courses (
student_id INT,
course_id INT,
enrollment_date DATE,
grade CHAR(2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Entity Relationship Diagram (ERD)
Visual representation of database structure showing entities, attributes, and relationships.
Design Best Practices
- Use meaningful names: Table and column names should be descriptive
- Be consistent: Use consistent naming conventions
- Avoid redundancy: Don't store the same data in multiple places
- Use appropriate data types: Choose the right data type for each column
- Define constraints: Use NOT NULL, UNIQUE, and CHECK constraints
Practical Example: E-commerce Database
-- Customers table
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Products table
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT DEFAULT 0,
category_id INT
);
-- Orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Practice Exercise
Design a simple library database with the following requirements:
- Track books (title, author, ISBN, publication year)
- Track library members (name, email, membership number)
- Track book loans (which member borrowed which book, when)
- A member can borrow multiple books
- A book can be borrowed by different members over time
Next Steps
In the next lesson, we'll learn about database normalization, a systematic approach to organizing data to reduce redundancy and improve data integrity.