# Many-to-many relationships · Relationships in Rell explained · Learn Chromia

> A many-to-many relationship links several records in one table to several records in another. A book, for example, can have more than one author, and one…

Source: https://chromia.com/learn/relationships-course/many-to-many/

Relationships in Rell explainedLesson 3 of 6

1. [01Relationships in Rell explained](https://chromia.com/learn/relationships-course/introduction/)
2. [02Creating Records](https://chromia.com/learn/relationships-course/joins/)
3. [03Many-to-many relationships](https://chromia.com/learn/relationships-course/many-to-many/)
4. [04One-to-many relationships](https://chromia.com/learn/relationships-course/one-to-many/)
5. [05One-to-one relationship](https://chromia.com/learn/relationships-course/one-to-one/)
6. [06Set up your project](https://chromia.com/learn/relationships-course/setup/)

[Learn](https://chromia.com/learn/)[Relationships in Rell explained](https://chromia.com/learn/relationships-course/)

03 / 06

# Many-to-many relationships

A many-to-many relationship links several records in one table to several records in another. A book, for example, can have more than one author, and one author can write many books. You normally model this with a junction table (also called a join table) whose foreign keys point to the primary keys of both related tables.

The next step is to define entities for these relationships. With them, a book can have several authors and an author can write several books. In the same way, a member can borrow several books, and a single book can be borrowed by several members. This keeps the library management application's data consistent and well organized.

Relational databases implement many-to-many relationships through a join table. In SQL, it looks like this:

sql

```
CREATE TABLE Author (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Book (
    id INT PRIMARY KEY,
    title VARCHAR(255)
);

CREATE TABLE Book_Author (
    book_id INT,
    author_id INT,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES Book(id),
    FOREIGN KEY (author_id) REFERENCES Author(id)
);

CREATE TABLE Member (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Loan (
    member_id INT,
    book_id INT,
    PRIMARY KEY (member_id, book_id),
    FOREIGN KEY (member_id) REFERENCES Member(id),
    FOREIGN KEY (book_id) REFERENCES Book(id)
);
```

Here is the Rell equivalent. Put the following code in the `entities.rell` file inside the `/src/library_management` directory:

/src/library\_management/entities.rell

```
entity author {
    key author_name: text;    // Name of the author
}

entity book_author {
    key book, author;  // Composite key to establish many-to-many relationship
}

entity member {
    key member_name: text;    // Name of the member
}

entity loan {
    key member, book;          // Composite key to establish many-to-many relationship
}
```

sql

```
create table "c0.author"(
  "rowid" bigint not null,
  "name" text not null,
  constraint "PK_c0.author"
    primary key ("rowid"),
  constraint "K_c0.author_0"
    unique ("name")
);

create table "c0.book_author"(
  "rowid" bigint not null,
  "book" bigint not null,
  "author" bigint not null,
  constraint "PK_c0.book_author"
    primary key ("rowid"),
  constraint "c0.book_author_book_FK"
    foreign key ("book")
    references "c0.book" ("rowid"),
  constraint "c0.book_author_author_FK"
    foreign key ("author")
    references "c0.author" ("rowid"),
  constraint "K_c0.book_author_0"
    unique (
      "book",
      "author"
    )
);

create table "c0.member"(
  "rowid" bigint not null,
  "name" text not null,
  constraint "PK_c0.member"
    primary key ("rowid"),
  constraint "K_c0.member_0"
    unique ("name")
);

create table "c0.loan"(
  "rowid" bigint not null,
  "member" bigint not null,
  "book" bigint not null,
  constraint "PK_c0.loan"
    primary key ("rowid"),
  constraint "c0.loan_member_FK"
    foreign key ("member")
    references "c0.member" ("rowid"),
  constraint "c0.loan_book_FK"
    foreign key ("book")
    references "c0.book" ("rowid"),
  constraint "K_c0.loan_0"
    unique (
      "member",
      "book"
    )
);
```

What each entity does:

* **Author**: Its key name makes every author unique.
* **Book\_Author**: The composite key key book, author connects books and authors in a many-to-many relationship.
* **Member**: Its key name makes every member unique.
* **Loan**: The composite key member, book connects members and books in a many-to-many relationship.

Next, put this code in the `operations.rell` file inside the `src/library_management/` directory:

src/library\_management/operations.rell

```
operation create_author(authors_name: text) {
    create author(authors_name);
}

operation create_book_author(title: text, authors_name: text) {
    val new_book = get_book_by_title(title);
    val new_author = get_author_by_name(authors_name);
    create book_author(new_book, new_author);
}

operation create_member(name: text) {
    create member(name);
}

operation create_loan(member_name: text, book_title: text) {
    val member = get_member_by_name(member_name);
    val book = get_book_by_title(book_title);
    create loan (member, book);
}
```

What each operation does:

* **create\_book\_author**: Creates a book with the given title and attaches it to the given shelf, then creates an author with the given name and links that author to the book.
* **create\_member**: Creates a member with the given name.
* **create\_loan:** Creates a loan that connects the given member and book.

note

You don't pass IDs to create dependent records. You pass the entity instances themselves when creating shelves, books, and so on, and Rell handles the linking for you.

To read existing records from the database, put this code in the `queries.rell` file inside the `src/library_management/` directory:

src/library\_management/queries.rell

```

query get_all_authors() = author @* { } ( .author_name );

query get_all_members() = member @* { } ( id = .rowid, .member_name );

query get_all_loans() = loan @* { } ( id = .rowid );

query get_member_by_name(name: text) = member @ { .member_name == name };

query get_author_by_name(author_name: text) = author @ { .author_name == author_name };

query get_all_books_with_authors() = book_author @* {} ( book_id = .book.rowid, author_id = .author.rowid);
```

## Testing

The tests from the previous lesson need to cover the new operations and queries. Open `src/test/my_library_test.rell` and replace everything in it with this code:

src/test/my\_library\_test.rell

```
@test module;

import library_management.{ create_library, get_all_library, create_shelf, get_all_shelves, create_book_author, get_all_books, get_all_authors, create_book, create_author, get_all_books_with_authors, create_member, get_all_members, create_loan, get_all_loans };

function test_create_library() {
    rell.test.tx()
        .op(create_library("Biblioteca Apostolica Vaticana", "Città del Vaticano", "Vatican", "00120"))
        .op(create_library("New York Public Library", "476 5th Ave", "New York", "10018"))
        .run();

    val all_libraries = get_all_library();
    assert_equals(all_libraries.size(), 2);

    val library_name = all_libraries[1].library_name;
    for (n in range(1, 4)) {
        rell.test.tx()
            .op(create_shelf(n, library_name))
            .run();
    }

    val all_shelves = get_all_shelves();
    assert_equals(all_shelves.size(), 3);

    rell.test.tx()
        .op(create_book("To Kill a Mockingbird", "New York Public Library", 1))
        .op(create_book("1984", "New York Public Library", 2))
        .op(create_book("Pride and Prejudice", "New York Public Library", 3))
        .op(create_book("The Great Gatsby", "New York Public Library", 3))
        .run();

    val all_books = get_all_books();
    assert_equals(all_books.size(), 4);

    rell.test.tx()
        .op(create_author("Harper Lee"))
        .op(create_author("George Orwell"))
        .op(create_author("Jane Austen"))
        .op(create_author("F. Scott Fitzgerald"))
        .run();

    val all_authors = get_all_authors();
    assert_equals(all_authors.size(), 4);

    rell.test.tx()
        .op(create_book_author("To Kill a Mockingbird", "Harper Lee"))
        .op(create_book_author("1984", "George Orwell"))
        .op(create_book_author("Pride and Prejudice", "Jane Austen"))
        .op(create_book_author("The Great Gatsby", "F. Scott Fitzgerald"))
        .run();

    val all_books_with_authors = get_all_books_with_authors();
    assert_equals(all_books_with_authors.size(), 4);

    rell.test.tx()
        .op(create_member("Erik"))
        .run();

    rell.test.tx()
        .op(create_member("Mark"))
        .run();

    val all_members = get_all_members();
    assert_equals(all_members.size(), 2);

    rell.test.tx()
        .op(create_loan("Erik", "To Kill a Mockingbird"))
        .op(create_loan("Erik", "1984"))
        .op(create_loan("Erik", "Pride and Prejudice"))
        .run();

    rell.test.tx()
        .op(create_loan("Mark", "The Great Gatsby"))
        .run();

    val all_loans = get_all_loans();
    assert_equals(all_loans.size(), 4);
}
```

From the project folder, run this command in the terminal to execute the tests:

shell

```nolines
chr test
```

A passing run produces output like this:

shell

```
====================Running unit tests====================
TEST: test.my_library_test:test_create_library
OK: test.my_library_test:test_create_library (3.020s)

------------------------------------------------------------
TEST RESULTS:

OK test.my_library_test:test_create_library

SUMMARY: 0 FAILED / 1 PASSED / 1 TOTAL (3.020s)

***** OK *****
```

## Creating records

You need a Chromia node running locally to work with the database. Start it without the `--wipe` flag so the records from earlier lessons are kept and available to the queries below.

shell

```nolines
chr node start
```

*If the node is already running, use this instead:*

shell

```nolines
chr node update
```

The examples below build two many-to-many relationships: `book - author` and `member - book`.

1. `book - author`: An author can have many books, and a book can have many authors.
2. `member - book`: A member can borrow many books, so the `loan` table creates a many-to-many relationship between `book` and `member` records.

Start by creating `author` records and linking them to the `book` records you created in the previous lesson.

Add a new author with the `create_author` operation:

shell

```nolines
chr tx --await create_author "Harper Lee"
```

shell

```nolines
chr tx --await create_author "George Orwell"
```

Next, link an author to a particular book with the `create_book_author` operation:

shell

```nolines
chr tx --await create_book_author "To Kill a Mockingbird" "Harper Lee"
```

shell

```nolines
chr tx --await create_book_author '"1984"' "George Orwell"
```

For the second many-to-many relationship, you first need a `member` record:

shell

```nolines
chr tx --await create_member "Erik"
```

shell

```nolines
chr tx --await create_member "Mark"
```

Last, connect the `member` record to a `book` record in the `loan` table by calling the `create_loan` operation.

shell

```nolines
chr tx --await create_loan "Mark" "To Kill a Mockingbird"
```

You now have both many-to-many relationships in place: `book - authors` and `member - books`.

note

The numbers passed in the call are the unique identifiers (ID) of the member and the book. You can look up each `id` with the `get_all_books` and `get_all_members` queries.

## Querying new records

Any of the queries defined earlier in this lesson can read back the records you just created.

To list every author, for example, call the `get_all_authors` query:

shell

```nolines
chr query get_all_authors
```

It returns:

json

```nolines
["Harper Lee", "George Orwell"]
```

To list every loan, call the `get_all_loans` query:

shell

```nolines
chr query get_all_loans
```

It returns:

json

```
[
  [
    "id": 11
  ],
  [
    "id": 13
  ]
]
```

tip

To check your work, try running the other queries defined earlier in this lesson.

Written for this site from Chromia’s public course. Code samples are the course’s own.

[← Previous**Creating Records**](https://chromia.com/learn/relationships-course/joins/)[Next →**One-to-many relationships**](https://chromia.com/learn/relationships-course/one-to-many/)