Datenbankbeziehungen: Eins-zu-viele und viele-zu-viele

Warum brauchen wir überhaupt Beziehungen?

Stellen Sie sich vor, Sie entwickeln einen Online-Shop. Sie haben Benutzer, die Bestellungen aufgeben. Natürlich ist es möglich, alle Daten in einer Tabelle zu speichern und die Informationen über den Benutzer in jeder Bestellung zu duplizieren. Aber das ist aus mehreren Gründen eine schlechte Idee:

Erstens werden Sie Daten duplizieren. Wenn sich die E-Mail-Adresse oder Adresse eines Benutzers ändert, müssen Sie die Datensätze in allen seinen Bestellungen aktualisieren. Zweitens nimmt es mehr Platz in der Datenbank ein. Drittens ist es leicht, einen Fehler zu machen und inkonsistente Daten zu erhalten.

Verknüpfungen lösen dieses Problem, indem sie es ermöglichen, Daten in separaten Tabellen zu speichern und sie über Fremdschlüssel zu verknüpfen.

Eins-zu-viele-Verbindung (One-to-Many)

Dies ist die häufigste Art der Kommunikation in Datenbanken. Die Essenz ist einfach: Ein Datensatz in der ersten Tabelle kann mit mehreren Datensätzen in der zweiten Tabelle verknüpft sein, aber der Datensatz in der zweiten Tabelle ist nur mit einem Datensatz in der ersten verknüpft.

Klassische Beispiele

Benutzer und Bestellungen. Ein Benutzer kann viele Bestellungen aufgeben, aber jede Bestellung gehört nur einem Benutzer.

Kategorien und Produkte. Eine Kategorie kann viele Produkte enthalten, aber jedes Produkt gehört nur zu einer Kategorie.

Autoren und Artikel. Ein Autor kann viele Artikel schreiben, aber jeder Artikel hat einen Autor.

Implementierung in SQL

Erstellen wir Tabellen, um Benutzer und Bestellungen zu verknüpfen:

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
);

Hier ist user_id in Tabelle orders ein Fremdschlüssel, der auf id in Tabelle users verweist. Der Parameter ON DELETE CASCADE bedeutet, dass beim Löschen eines Benutzers auch alle seine Bestellungen automatisch gelöscht werden.

Arbeit mit Daten

Wir fügen einen Benutzer und einige seiner Bestellungen hinzu:

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');

Um alle Bestellungen des Benutzers zusammen mit seinen Daten zu erhalten, verwenden wir 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;

Arbeit in der App

Wenn Sie ORM verwenden, z. B. TypeORM oder Sequelize, wird die Eins-zu-Viele-Beziehung deklarativ konfiguriert:

// 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;
}

Jetzt können Sie ganz einfach einen Benutzer mit allen seinen Bestellungen abrufen:

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

console.log(user.orders); // Array aller Benutzerbestellungen

Viele-zu-viele-Beziehung

Die Viele-zu-Viele-Beziehung ist etwas komplizierter. Hier kann ein Eintrag in der ersten Tabelle mit mehreren Einträgen in der zweiten Tabelle verknüpft werden und umgekehrt.

Typische Szenarien

Studenten und Kurse. Ein Student kann sich für mehrere Kurse einschreiben, und viele Studenten können an einem Kurs teilnehmen.

Produkte und Tags. Ein Produkt kann mehrere Tags haben und ein Tag kann auf mehrere Produkte angewendet werden.

Benutzer und Rollen. Ein Benutzer kann mehrere Rollen haben, und eine Rolle kann mehreren Benutzern zugewiesen werden.

Zwischentabelle

In relationalen Datenbanken wird viele-zu-viele durch eine Zwischentabelle implementiert, die die Fremdschlüssel beider verknüpfter Tabellen enthält.

Erstellen wir ein Tag-System für Produkte:

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
);

Tabelle product_tags ist die Zwischentabelle. Sie verbindet Produkte und Tags. Achten Sie auf den zusammengesetzten Primärschlüssel: Die Kombination aus product_id und tag_id muss eindeutig sein, um zu verhindern, dass ein und dasselbe Tag zweimal zu einem Produkt hinzugefügt wird.

Hinzufügen von Daten

-- Добавляем товары
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 - Премиум

Datenabfragen

Wir erhalten alle Produkte mit einem bestimmten 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';

Wir erhalten alle Tags für ein bestimmtes Produkt:

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

Wir finden Produkte, die zwei bestimmte Tags haben:

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 und viele-zu-vielen

ORM macht die Arbeit mit solchen Verbindungen einfacher:

@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[];
}

Arbeit mit Daten:

// Wir erstellen ein Produkt mit 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);

// Wir erhalten die Ware mit allen Tags
const productWithTags = await productRepository.findOne({
    where: { id: 1 },
    relations: ['tags']
});

Zwischentabelle mit zusätzlichen Daten

Manchmal müssen in einer Zwischentabelle nicht nur Verbindungen, sondern auch zusätzliche Informationen gespeichert werden. Zum Beispiel müssen wir im Kurssystem möglicherweise das Datum der Einschreibung und den Fortschritt des Studenten kennen:

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)
);

Hier ist enrollments nicht mehr nur eine verbindende Tabelle, sondern eine vollwertige Entität mit eigenen Attributen.

In TypeORM muss eine solche Verbindung explizit beschrieben werden:

@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[];
}

Leistung und Optimierung

Indizes

Erstellen Sie immer Indizes für Fremdschlüssel. Dies ist entscheidend für die Leistung von JOIN-Abfragen:

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

Dies ist ein klassisches Problem bei der Arbeit mit Verbindungen. Stellen Sie sich vor, Sie erhalten eine Liste von 100 Benutzern und laden dann deren Bestellungen mit einer separaten Anfrage für jeden Benutzer hoch. Es stellt sich heraus, dass 101 Anfragen an die Datenbank gestellt werden.

Die Lösung ist die Verwendung von Eager Loading:

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

// Gut - eine Anfrage mit JOIN
const users = await userRepository.find({
    relations: ['orders']
});

Lazy Loading vs. Greedy Loading

Es ist nicht immer notwendig, alle zugehörigen Daten herunterzuladen. Wenn ein Benutzer Tausende von Bestellungen hat und Sie nur grundlegende Informationen über ihn benötigen, laden Sie nicht alle Bestellungen auf einmal. Tun Sie dies auf Anfrage:

// Nur Benutzer wird geladen
const user = await userRepository.findOne({ where: { id: 1 } });

// Später, wenn nötig, Bestellungen hochladen
if (needOrders) {
    const orders = await orderRepository.find({ 
        where: { userId: user.id },
        take: 10,
        order: { createdAt: 'DESC' }
    });
}

Häufige Fehler

Keine Integritätsbeschränkungen

Wenn Sie FOREIGN KEY und ON DELETE CASCADE nicht verwenden, können Sie "hängende" Datensätze erhalten - Bestellungen, die sich auf nicht vorhandene Benutzer beziehen.

-- Плохо
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
);

Falsche Auswahl des Verbindungstyps

Manchmal verwenden Entwickler viele-zu-viele, wo eins-zu-viele ausreicht, was die Struktur unnötig kompliziert macht. Oder umgekehrt versuchen sie, mit Eins-zu-Viele auszukommen, obwohl die Geschäftslogik Viele-zu-Viele erfordert.

Analysieren Sie immer den Fachbereich: Kann Entität A mehrere Beziehungen zu Entität B haben und umgekehrt?

Datenredundanz

Anfänger duplizieren manchmal Daten, anstatt Verknüpfungen zu erstellen:

-- Плохо
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)
);

Wenn eine Denormalisierung gerechtfertigt ist

Trotz aller Vorteile der Normalisierung ist es manchmal sinnvoll, die Daten für die Leistung etwas zu denormalisieren. Wenn Sie beispielsweise ständig die Anzahl der Bestellungen eines Benutzers kennen müssen, können Sie einen Zähler hinzufügen:

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();

Aber denken Sie daran: Denormalisierung ist ein Kompromiss zwischen Lesegeschwindigkeit und Unterstützungskomplexität. Verwenden Sie sie nur dort, wo es wirklich nötig ist.

Befund

Das Verständnis der Beziehungen zwischen Tabellen ist die Grundlage für die Arbeit mit relationalen Datenbanken. Die Eins-zu-Viele-Beziehung deckt die meisten Szenarien ab und ist einfach zu implementieren. Viele-zu-vielen erfordert eine Zwischentabelle, bietet aber Flexibilität bei der Modellierung komplexer Beziehungen.

Wichtige Punkte, die Sie beachten sollten: Verwenden Sie immer Fremdschlüssel, um die Datenintegrität aufrechtzuerhalten, erstellen Sie Indizes, um Abfragen zu optimieren, achten Sie auf das N+1-Problem und wählen Sie den richtigen Verbindungstyp basierend auf der Geschäftslogik Ihrer Anwendung.

Anlage Kodik ist eine Bildungsplattform für angehende Entwickler, auf der Sie strukturierte Kurse in Python, JavaScript, HTML, CSS und anderen Programmiertechnologien finden.

Wir haben ein aktives Telegram-Community, wo Entwickler sich gegenseitig bei der Lösung von Problemen helfen, Erfahrungen austauschen und neue Technologien diskutieren. Treten Sie bei Kodik, um Programmieren in einem komfortablen Tempo mit Unterstützung erfahrener Mentoren und Gleichgesinnter zu lernen!

Üben Sie an realen Aufgaben, und mit der Zeit werden Sie intuitiv verstehen, welche Datenstruktur für eine bestimmte Situation am besten geeignet ist. Viel Glück beim Entwickeln!