Database relationships: one-to-many and many-to-many

Why do we need connections at all?

Imagine that you are developing an online store. You have users who place orders. You can, of course, store all the data in one table, duplicating the user information in each order. But this is a bad idea for several reasons:

First, you will duplicate the data. If the user changes their email or address, you will have to update the records in all their orders. Secondly, it takes up more space in the database. Third, it is easy to make a mistake and get inconsistent data.

Relationships solve this problem by allowing you to store data in separate tables and link them through foreign keys.

One-to-Many

This is the most common type of connection in databases. The idea is simple: one record in the first table can be linked to several records in the second table, but a record in the second table is linked to only one record in the first.

Classic examples

Users and orders. One user can make many orders, but each order belongs to only one user.

Categories and products. One category can contain many products, but each product belongs to only one category.

Authors and articles. One author can write many articles, but each article has one author.

Implementation in SQL

Let's create tables to link users and orders:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Here user_id in the table orders is a foreign key that references id in the table users. The ON DELETE CASCADE parameter means that when a user is deleted, all their orders will also be deleted automatically.

Data processing

Let's add a user and a few of their orders:

INSERT INTO users (name, email) 
VALUES ('Alexey Ivanov', 'alexey@example.com');

INSERT INTO orders (user_id, total_amount, status) 
VALUES 
    (1, 1500.00, 'completed'),
    (1, 2300.50, 'pending'),
    (1, 890.00, 'completed');

To get all the user's orders along with their data, use JOIN:

SELECT 
    users.name,
    users.email,
    orders.id as order_id,
    orders.total_amount,
    orders.status
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE users.id = 1;

Work in the application

If you use ORM, for example, TypeORM or Sequelize, the one-to-many relationship is configured declaratively:

// TypeORM
@Entity()
class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column()
    email: string;

    @OneToMany(() => Order, order => order.user)
    orders: Order[];
}

@Entity()
class Order {
    @PrimaryGeneratedColumn()
    id: number;

    @Column('decimal')
    totalAmount: number;

    @Column()
    status: string;

    @ManyToOne(() => User, user => user.orders)
    user: User;

    @Column()
    userId: number;
}

Now you can easily get a user with all their orders:

const user = await userRepository.findOne({
    where: { id: 1 },
    relations: ['orders']
});

console.log(user.orders); // Array of all user orders

Many-to-Many

The many-to-many relationship is a little more complicated. Here, one entry in the first table can be associated with multiple entries in the second table, and vice versa.

Typical scenarios

Students and courses. One student can enroll in several courses, and many students can study in one course.

Products and tags. One product can have multiple tags, and one tag can be applied to multiple products.

Users and roles. One user can have multiple roles, and one role can be assigned to multiple users.

Intermediate table

In relational databases, many-to-many is implemented through an intermediate table that contains the foreign keys of both linked tables.

Let's create a tag system for products:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE product_tags (
    product_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (product_id, tag_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

Table product_tags is the intermediate table. It links products and tags. Pay attention to the composite primary key: the combination of product_id and tag_id must be unique, which prevents adding the same tag to the product twice.

Adding data

-- Добавляем товары
INSERT INTO products (name, price, description) 
VALUES 
    ('MacBook Pro', 150000, 'Laptop for developers'),
    ('iPhone 15', 80000, 'Latest generation smartphone'),
    ('iPad Air', 60000, 'Work tablet');

-- Добавляем теги
INSERT INTO tags (name) 
VALUES 
    ('Apple'),
    ('Electronics'),
    ('For work'),
    ('Premium');

-- Связываем товары с тегами
INSERT INTO product_tags (product_id, tag_id) 
VALUES 
    (1, 1), -- MacBook Pro - Apple
    (1, 2), -- MacBook Pro - Электроника
    (1, 3), -- MacBook Pro - Для работы
    (1, 4), -- MacBook Pro - Премиум
    (2, 1), -- iPhone 15 - Apple
    (2, 2), -- iPhone 15 - Электроника
    (2, 4); -- iPhone 15 - Премиум

Data requests

We will get all the products with a certain tag:

SELECT products.*
FROM products
INNER JOIN product_tags ON products.id = product_tags.product_id
INNER JOIN tags ON product_tags.tag_id = tags.id
WHERE tags.name = 'Apple';

We will get all the tags for a specific product:

SELECT tags.*
FROM tags
INNER JOIN product_tags ON tags.id = product_tags.tag_id
WHERE product_tags.product_id = 1;

Let's find products that have two specific tags:

SELECT products.*
FROM products
WHERE id IN (
    SELECT product_id 
    FROM product_tags
    INNER JOIN tags ON product_tags.tag_id = tags.id
    WHERE tags.name IN ('Apple', 'For work')
    GROUP BY product_id
    HAVING COUNT(DISTINCT tags.id) = 2
);

ORM and many-to-many

ORM makes it easier to work with such connections:

@Entity()
class Product {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column('decimal')
    price: number;

    @ManyToMany(() => Tag, tag => tag.products)
    @JoinTable({
        name: 'product_tags',
        joinColumn: { name: 'product_id' },
        inverseJoinColumn: { name: 'tag_id' }
    })
    tags: Tag[];
}

@Entity()
class Tag {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToMany(() => Product, product => product.tags)
    products: Product[];
}

Data processing:

// Create a product with tags
const product = new Product();
product.name = 'MacBook Pro';
product.price = 150000;

const tag1 = await tagRepository.findOne({ where: { name: 'Apple' } });
const tag2 = await tagRepository.findOne({ where: { name: 'Premium' } });

product.tags = [tag1, tag2];
await productRepository.save(product);

// We receive the goods with all tags
const productWithTags = await productRepository.findOne({
    where: { id: 1 },
    relations: ['tags']
});

Intermediate table with additional data

Sometimes you need to store not only links, but also additional information in the intermediate table. For example, in the course system, we may need to know the date of the student's enrollment and his progress:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    duration_hours INTEGER NOT NULL
);

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    progress INTEGER DEFAULT 0,
    completed BOOLEAN DEFAULT false,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE(student_id, course_id)
);

Here enrollments is no longer just a linking table, but a full-fledged entity with its own attributes.

In TypeORM, such a relationship must be described explicitly:

@Entity()
class Enrollment {
    @PrimaryGeneratedColumn()
    id: number;

    @ManyToOne(() => Student, student => student.enrollments)
    student: Student;

    @ManyToOne(() => Course, course => course.enrollments)
    course: Course;

    @Column()
    enrolledAt: Date;

    @Column()
    progress: number;

    @Column()
    completed: boolean;
}

@Entity()
class Student {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany(() => Enrollment, enrollment => enrollment.student)
    enrollments: Enrollment[];
}

@Entity()
class Course {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    title: string;

    @OneToMany(() => Enrollment, enrollment => enrollment.course)
    enrollments: Enrollment[];
}

Performance and optimization

Indexes

Always create indexes for foreign keys. This is critical for the performance of JOIN queries:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_product_tags_product_id ON product_tags(product_id);
CREATE INDEX idx_product_tags_tag_id ON product_tags(tag_id);

N+1 problem

This is a classic problem when working with connections. Imagine that you receive a list of 100 users, and then you load their orders for each of them with a separate request. This results in 101 queries to the database.

The solution is to use eager loading:

// Bad - N+1 problem
const users = await userRepository.find();
for (const user of users) {
    user.orders = await orderRepository.find({ where: { userId: user.id } });
}

// Good - one request with JOIN
const users = await userRepository.find({
    relations: ['orders']
});

Lazy loading vs greedy loading

You don't always need to load all related data. If a user has thousands of orders and you only need basic information about them, do not load all orders at once. Do it on demand:

// Loading only the user
const user = await userRepository.findOne({ where: { id: 1 } });

// Later, if necessary, we load orders
if (needOrders) {
    const orders = await orderRepository.find({ 
        where: { userId: user.id },
        take: 10,
        order: { createdAt: 'DESC' }
    });
}

Common mistakes

No integrity restrictions

If you do not use FOREIGN KEY and ON DELETE CASCADE, you can get "hanging" records — orders that refer to non-existent users.

-- Плохо
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2)
);

-- Хорошо
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Incorrect choice of connection type

Sometimes developers use many-to-many where one-to-many is enough, complicating the structure unnecessarily. Or vice versa, they try to do one-to-many, although business logic requires many-to-many.

Always analyze the subject area: can entity A have multiple relationships with entity B, and vice versa?

Data duplication

Beginner developers sometimes duplicate data instead of creating links:

-- Плохо
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_name VARCHAR(100),
    user_email VARCHAR(100),
    user_phone VARCHAR(20),
    total_amount DECIMAL(10, 2)
);

-- Хорошо
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

When denormalization is justified

Despite all the benefits of normalization, it sometimes makes sense to denormalize the data a little for performance. For example, if you constantly need to know the number of user orders, you can add a counter:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    orders_count INTEGER DEFAULT 0
);

-- Триггер для автоматического обновления счётчика
CREATE OR REPLACE FUNCTION update_orders_count()
RETURNS TRIGGER AS $
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET orders_count = orders_count + 1 
        WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET orders_count = orders_count - 1 
        WHERE id = OLD.user_id;
    END IF;
    RETURN NULL;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER orders_count_trigger
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_orders_count();

But remember: denormalization is a compromise between reading speed and support complexity. Use it only where it is really needed.

Conclusion

Understanding the relationships between tables is the foundation of working with relational databases. One-to-many relationship covers most scenarios and is easy to implement. Many-to-many requires an intermediate table, but gives flexibility in modeling complex relationships.

Key points to remember: always use foreign keys to maintain data integrity, create indexes to optimize queries, be careful about the N+1 problem, and choose the right type of connection based on the business logic of your application.

Appendix Code is an educational platform for beginner developers, where you will find structured courses in Python, JavaScript, HTML, CSS and other programming technologies.

We have created an active Telegram community, where developers help each other solve problems, share experiences and discuss new technologies. Join Codiceto learn programming at a comfortable pace with the support of experienced mentors and like-minded people!

Practice on real tasks, and over time you will intuitively understand which data structure is best suited for a particular situation. Good luck with your development!