View source for 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 <code>id</code> field:<syntaxhighlight lang="mysql">
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);
</syntaxhighlight>

=== Foreign Keys ===
These are fields that link one table to another. For example, in the orders table, we store the <code>id</code> of the user:<syntaxhighlight lang="mysql">
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)
);
</syntaxhighlight>

== Types of Relationships Between Tables ==

=== One-to-One ===
For example, a user can have one profile with additional information:<syntaxhighlight lang="mysql">
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    address TEXT,
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
</syntaxhighlight>

=== 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:<syntaxhighlight lang="mysql">
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)
);
</syntaxhighlight>

== Advantages of the Relational Model ==

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

== Practical Example ==
Let’s consider a simple blog system:<syntaxhighlight lang="mysql">
-- 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)
);
</syntaxhighlight>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.

[[wj-ru:Реляционные базы данных для начинающих]]
[[wj-de:Relationale Datenbanken für Anfänger]] 
[[wj-fr:Bases de données relationnelles pour débutants]] 
[[wj-it:Basi di dati relazionali per principianti]]
[[wj-es:Bases de datos relacionales para principiantes]]