Skip to main content

Many-to-many relationships

A many-to-many relationship exists when multiple records in one table are associated with multiple records in another table. For example, a book can have multiple authors, and an author can write multiple books. This relationship is typically implemented using a junction table (or join table) that contains foreign keys referencing the primary keys of the two tables involved in the relationship.

Now, let’s define entities that represent many-to-many relationships. These relationships ensure that each book can have multiple authors, and each author can write multiple books. Similarly, each member can borrow multiple books, and multiple members can borrow each book. This structure maintains the integrity and organization of the data within our library management application.

In a traditional relational database, many-to-many relationships are established using a join table. Here’s how it would look in 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)
);

Let’s take a look at how it would be in Rell. Add the following code to entities.rell file, stored in 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
}

In this code:

  • Author: The key name ensures that each author is unique.
  • Book_Author: The composite key key book, author establishes a many-to-many relationship between books and authors.
  • Member: The key name ensures that each member is unique.
  • Loan: The composite key member, book establishes a many-to-many relationship between members and books.

Add the following code to the operations.rell file stored in 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);
}

Description of each operation is as follows:

  • create_book_author: This operation creates a book with the provided title and links it to the specified shelf. It then creates an author with the provided name and establishes a relationship between the book and the author.
  • create_member: This operation creates a member with the provided name.
  • create_loan: This operation creates a loan linking the provided member and book.
note

Note that we don't need to use IDs to create dependent records; instead, we directly use instances when creating shelves, books, etc., and Rell automatically links the records under the hood.

To query the existing records from the database, add the following code to the queries.rell file located in 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

In this lesson, we must extend the testing functionality that was added in the previous lesson to accomodate the newly created operations and queries. Open the src/test/my_library_test.rell file and replace its contents with the following 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);
}

Then you have to run the following command in the terminal from the project folder to test the code:

chr test

The result of the successful test looks as follows:

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

To work with the database, you need to run the Chromia node on your local machine. Start the node without the --wipe flag to ensure that the records created in previous lessons are retained and can be used with the queries provided below.

chr node start

Or if the node is already running:

chr node update

The examples provided below aim to create two many-to-many relationships: book - author and member - book.

  1. book - author: Each author can have multiple books, and each book can have multiple authors.

  2. member - book: Each member can borrow multiple books. Therefore, the loan table establishes a many-to-many relationship between book and member records.

First, we need to create the author records and link them to the existing book records created in the previous lesson.

Use the create_author operation to add a new author to the database:

chr tx --await create_author "Harper Lee"
chr tx --await create_author "George Orwell"

Then we can begin creating a many-to-many record that links an author to a specific book by using the create_book_author operation:

chr tx --await create_book_author "To Kill a Mockingbird" "Harper Lee"
chr tx --await create_book_author '"1984"' "George Orwell"

Afterwards, to create another many-to-many relationship we must first add a new member record:

chr tx --await create_member "Erik"
chr tx --await create_member "Mark"

Finally, to establish a new many-to-many relationship, we need to link the member record with the book record in the loan data table. Use the create_loan operation for this purpose.

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

At this stage, we have successfully created many-to-many relationships: book - authors and member - books.

note

The numbers in the invocation represent the unique identifiers (ID) of the member and the book. To retrieve the id, use the get_all_books and get_all_members queries.

Querying new records

To retrieve the newly created records, we can use a variety of queries provided in the beginning of this lesson.

For instance, to get all existing authors, you can take advantage of the get_all_authors query:

chr query get_all_authors

The result of this operation is as follows:

["Harper Lee", "George Orwell"]

In case you want to retreive all loans, then you can use the get_all_loans query:

chr query get_all_loans

This query returns the following result:

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

For testing purposes, you can invoke the rest of the queries provided in the beginning of the lesson.