Chromia
Start Building

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}

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.

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
chr test

A passing run produces output like this:

shell
====================Running unit tests====================TEST: test.my_library_test:test_create_libraryOK: test.my_library_test:test_create_library (3.020s)------------------------------------------------------------TEST RESULTS:OK test.my_library_test:test_create_librarySUMMARY: 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
chr node start

If the node is already running, use this instead:

shell
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
chr tx --await create_author "Harper Lee"
shell
chr tx --await create_author "George Orwell"

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

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

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

shell
chr tx --await create_member "Erik"
shell
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
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.

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
chr query get_all_authors

It returns:

json
["Harper Lee", "George Orwell"]

To list every loan, call the get_all_loans query:

shell
chr query get_all_loans

It returns:

json
[  [    "id": 11  ],  [    "id": 13  ]]
Written for this site from Chromia’s public course. Code samples are the course’s own.