Why is a relation needed in a table?

A relation, or connection between tables in a database, is needed for:

• Eliminating data redundancy: Instead of duplicating information in multiple tables, it is stored in one, and others refer to it. This saves space and simplifies updates.
• Ensuring data integrity: Relations help maintain data consistency. For example, you cannot delete a row in one table if it is referenced by a row in another (depending on the type of constraint).
• Simplifying queries: Allows easy retrieval of related data from multiple tables using JOIN operations.
• Creating the database structure: Reflects the logical relationship between real-world entities, making the database structure more understandable and organized.

Examples of relation types:

• One-to-one: One row in table A is related to only one row in table B, and vice versa.
• One-to-many: One row in table A can be related to multiple rows in table B, but a row in B is related to only one row in A.
• Many-to-many: A row in table A can be related to multiple rows in table B, and vice versa. Usually implemented through an intermediate table.

Implementation example in SQL:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) // Relation "one-to-many": one customer can have many orders
);