Philip: Created page with "== 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) =..."

2025-01-15T22:17:21Z

Created page with "== 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) =..."

New page

== 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]]

Relational databases for beginners - Revision history