BLOG POSTS

MangoHost Blog / Understanding Relational Databases: Basics and Concepts

Understanding Relational Databases: Basics and Concepts

Relational databases form the backbone of most modern applications, storing structured data in tables with well-defined relationships. Whether you’re building a web app, managing user accounts, or handling business transactions, understanding how relational databases work is essential for any technical professional. This guide covers the fundamental concepts, implementation strategies, and practical considerations you’ll need to effectively design, deploy, and maintain relational database systems.

How Relational Databases Work

At its core, a relational database organizes data into tables (relations) consisting of rows (records) and columns (fields). Each table represents an entity, and relationships between tables are established through foreign keys that reference primary keys in other tables.

The relational model follows several key principles:

Atomicity: Each field contains indivisible values
Uniqueness: Each row is uniquely identifiable through a primary key
Consistency: Data integrity is maintained through constraints and rules
Normalization: Data redundancy is minimized through proper table design

SQL (Structured Query Language) serves as the standard interface for interacting with relational databases. Here’s a basic example of creating and querying tables:

-- Create a users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create an orders table with foreign key relationship
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_total DECIMAL(10,2),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Query with JOIN to get user orders
SELECT u.username, o.order_total, o.order_date 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE u.username = 'john_doe';

Setting Up Your First Relational Database

Let’s walk through setting up MySQL, one of the most popular relational database systems, on a Linux server:

# Install MySQL on Ubuntu/Debian
sudo apt update
sudo apt install mysql-server

# Secure the installation
sudo mysql_secure_installation

# Connect to MySQL
mysql -u root -p

# Create a new database and user
CREATE DATABASE myapp_db;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;

For development environments, you can also use Docker for quick setup:

# Run MySQL in Docker container
docker run --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -e MYSQL_DATABASE=testdb \
  -e MYSQL_USER=devuser \
  -e MYSQL_PASSWORD=devpass \
  -p 3306:3306 \
  -d mysql:8.0

# Connect to the containerized MySQL
docker exec -it mysql-dev mysql -u devuser -p testdb

Database Design and Normalization

Proper database design is crucial for performance and maintainability. The normalization process eliminates data redundancy and ensures consistency. Here’s an example of transforming an unnormalized table into a properly structured relational design:

Before normalization (problematic):

-- Poor design with repeated data
CREATE TABLE customer_orders (
    order_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    quantity INT
);

After normalization (Third Normal Form):

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20)
);

-- Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order items table (many-to-many relationship)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Popular Relational Database Comparison

Database	License	Max DB Size	ACID Compliance	Best Use Case	Performance
MySQL	GPL/Commercial	256TB	Yes (InnoDB)	Web applications	High read performance
PostgreSQL	Open Source	32TB	Yes	Complex queries, OLAP	Excellent for complex operations
SQLite	Public Domain	281TB	Yes	Embedded applications	Fast for single-user scenarios
SQL Server	Commercial	524PB	Yes	Enterprise applications	Optimized for Windows
Oracle	Commercial	4PB	Yes	Large enterprises	High-end performance features

Real-World Use Cases and Examples

E-commerce Platform: A typical online store requires multiple related tables for products, customers, orders, inventory, and payment processing. Here’s a practical schema:

-- Product catalog with categories
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_sku (sku),
    INDEX idx_category (category_id)
);

-- Shopping cart implementation
CREATE TABLE cart_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(128) NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_session (session_id)
);

User Management System: Most applications need robust user authentication and authorization:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

Performance Optimization and Indexing

Database performance depends heavily on proper indexing strategy. Here are practical indexing examples:

-- Create indexes for frequently queried columns
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_product_category ON products(category_id, price);

-- Composite index for complex queries
CREATE INDEX idx_user_orders ON orders(user_id, order_date DESC);

-- Analyze query performance
EXPLAIN SELECT u.username, COUNT(o.id) as order_count 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.created_at >= '2023-01-01' 
GROUP BY u.id 
ORDER BY order_count DESC;

Performance benchmarking results show significant improvements with proper indexing:

Query Type	Without Index	With Index	Improvement
SELECT by email	245ms	2ms	122x faster
ORDER BY date	1.2s	15ms	80x faster
JOIN with WHERE	890ms	28ms	32x faster

Common Issues and Troubleshooting

Connection Pool Exhaustion: Applications often run into connection limits under high load:

# Check current connections in MySQL
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';

# Optimize connection pooling in application
# Example for Node.js with mysql2
const pool = mysql.createPool({
  host: 'localhost',
  user: 'myapp_user',
  password: 'password',
  database: 'myapp_db',
  connectionLimit: 10,
  acquireTimeout: 60000,
  timeout: 60000
});

Deadlock Issues: Multiple transactions can lock each other out:

-- Check for deadlocks
SHOW ENGINE INNODB STATUS;

-- Prevention strategy: consistent lock ordering
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table2 WHERE id = 2 FOR UPDATE;
-- Perform updates
COMMIT;

Slow Query Identification: Enable and analyze slow query logs:

# Enable slow query log in MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# Analyze slow queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Best Practices and Security Considerations

Security should be built into your database design from the start:

-- Create application-specific users with limited privileges
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp_db.* TO 'app_readonly'@'%';

CREATE USER 'app_writer'@'%' IDENTIFIED BY 'another_secure_password';
GRANT SELECT, INSERT, UPDATE ON myapp_db.* TO 'app_writer'@'%';

-- Never grant DELETE or DROP to application users
-- Use prepared statements to prevent SQL injection
PREPARE stmt FROM 'SELECT * FROM users WHERE email = ? AND status = ?';
SET @email = 'user@example.com';
SET @status = 'active';
EXECUTE stmt USING @email, @status;

Additional security measures include:

Enable SSL/TLS encryption for database connections
Regular security updates and patches
Database firewall rules restricting access
Regular backups with tested restore procedures
Audit logging for sensitive operations

Backup strategy example:

# Automated backup script
#!/bin/bash
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create compressed backup
mysqldump --single-transaction --routines --triggers \
  -u backup_user -p myapp_db | \
  gzip > "$BACKUP_DIR/myapp_db_$DATE.sql.gz"

# Keep only last 7 days of backups
find $BACKUP_DIR -name "myapp_db_*.sql.gz" -mtime +7 -delete

Understanding relational databases provides a solid foundation for building scalable, reliable applications. The concepts covered here apply across different database systems, though specific syntax and features may vary. For deeper technical details, consult the official documentation for MySQL, PostgreSQL, or your chosen database system. As you gain experience, explore advanced topics like replication, sharding, and database clustering to handle enterprise-scale requirements.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.