Relational databases for beginners

What is a Relational Database?

Imagine you are running an online store. You have information about customers, their orders, and products. How can you organize all this data? A relational database allows you to store information in related tables, similar to Excel spreadsheets, but much more powerful.

Key Concepts

Tables

Each table represents a specific entity. For example:

• Users table (users)
• Orders table (orders)
• Products table (products)

Fields (Columns)

Each table consists of fields. For example, for the users table, these could be:

• id (unique identifier)
• name
• email
• phone
• registration date

Records (Rows)

These are the actual data in a table. One row = one record about a user, an order, or a product.

How are Tables Related to Each Other?

Primary Keys

Each table should have a unique identifier for each record. Typically, this is the id field:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

Foreign Keys

These are fields that link one table to another. For example, in the orders table, we store the id of the user:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Types of Relationships Between Tables

One-to-One

For example, a user can have one profile with additional information:

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    address TEXT,
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

One-to-Many

The most common type of relationship. For example, one user can have many orders:

• One user → Many orders
• One product → Many reviews
• One category → Many products

Many-to-Many

For example, one order can contain many products, and one product can appear in multiple orders. For this, an intermediate table is created:

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    PRIMARY KEY (order_id, product_id)
);

Advantages of the Relational Model

1. Data Integrity: The system ensures that all relationships are valid.
2. No Duplication: Information is stored in one place and linked through keys.
3. Flexibility: Easy to add new relationships and modify the structure.
4. Efficiency: Fast data search and processing thanks to indexes.

Practical Example

Let’s consider a simple blog system:

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME
);

-- Posts table
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(200),
    content TEXT,
    published_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Comments table
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    comment_text TEXT,
    created_at DATETIME,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

In this example:

• A user can create multiple posts (one-to-many)
• Each post can have multiple comments (one-to-many)
• Each comment is linked to both a post and a user

Conclusion

Relational databases are a powerful tool for organizing data in an application. Understanding the basic principles of how they work is crucial for beginner developers. A well-designed database structure makes an application more reliable, efficient, and easier to maintain.