Skip to main content

One-to-many relationships

One-to-many relationships represent a single record in one table linked to multiple records in another. For instance, in a library system, a single library can have numerous shelves, but each shelf belongs to only one library. This type of relationship is typically implemented using foreign keys in the child table (the table representing the “many” side of the relationship).

To better illustrate this concept, let’s explore how to define these relationships using SQL. We’ll consider the relationships between Library and Shelf, and Shelf and Book.

Here’s how it would look in SQL:

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

CREATE TABLE Shelf (
    id INT PRIMARY KEY,
    number INT,
    library_id INT,
    FOREIGN KEY (library_id) REFERENCES Library(id)
);

CREATE TABLE Book (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    shelf_id INT,
    FOREIGN KEY (shelf_id) REFERENCES Shelf(id)
);

In this example:

  • Library table: Contains information about libraries.
  • Shelf table: Includes a foreign key library_id to link each shelf to a specific library.
  • Book table: Has a foreign key shelf_id to associate each book with a specific shelf.

While this setup is effective, it requires careful management of foreign keys and constraints to ensure data integrity. Each step involves meticulous SQL syntax to maintain the relationships between tables. Defining the same entities and their relationships in Rell is more streamlined and expressive. Rell abstracts much of the complexity, making the code cleaner and easier to understand.

Add the following code to the src/library_management/entities.rell file:

src/library_management/entities.rell
entity shelf {
    number: integer;    // Shelf number
    index library;      // Many-to-one relationship with Library
}

entity book {
    title: text;    // Title of the book
    index shelf;    // Many-to-one relationship with Shelf
}

Next, we have to add an operation that creates a new shelf in a specific library and an operation that creates a new book and assigns it to a particular shelf. Add the following code to the operations.rell file in the src/library_management folder:

src/library_management/operations.rell
operation create_shelf(number: integer, library_name: text) {
    val library = get_library_by_name(library_name);
    create shelf(number, library);
}

operation create_book(title: text, library_name: text, shelf_number: integer) {
    val library = get_library_by_name(library_name);
    val shelf = get_shelf_by_number(library, shelf_number);
    create book(title, shelf);
}

In this code:

  • Library and shelf: Each library can contain multiple shelves. The index library in the shelf entity establishes this relationship.
  • Shelf and book: Each shelf can contain multiple books. The index shelf in the book entity establishes this relationship.

In Rell, an index facilitates fast querying of entities based on specific attributes. Simply put, an index is a pointer to data in a table. Creating an index on specific attributes significantly speeds up queries that filter or retrieve entities based on those attributes. Think of them as specialized pointers that guide you directly to the relevant data.

For testing purposes, create the following queries to retrieve records. Add the following code to the queries.rell file:

src/library_management/queries.rell
query get_shelf_by_library_name(id: rowid) = shelf @ { .rowid == id };

query get_book_by_title(title: text) = book @ { .title == title };

query get_shelf_by_number(library, number: integer) = shelf @ { .library == library, .number == number };

query get_all_shelves() = shelf @* { } ( id = .rowid, library_name = .library.library_name, .number );

query get_all_books() = book @* { } ( id = .rowid, .title );

query get_libraries_with_shelves() {
    return (l: library, @outer s: shelf @* { s.library == l }) @* {} ( @group l.library_name, @list shelves = s?.number );
}

Testing

In this lesson, we must extend the testing functionality that was added in the previous lesson to accommodate 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, get_all_books };

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

    val all_books = get_all_books();
    assert_equals(all_books.size(), 3);
}

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 (1.771s)

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

OK test.my_library_test:test_create_library

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

***** OK *****

Creating Records

To interact with the database, you must run the Chromia node locally. We start the node without the --wipe flag because we need records created in the previous lesson to use the queries below.

chr node start

If the node is already running, you can instead apply changes with:

chr node update

For more information, refer to the Chromia CLI Node Documentation.

To create a one-to-many relationship, we must generate two data tables: shelf and book. In this context, records in the shelf data table are linked to specific library records, forming a one-to-many relationship. Similarly, records stored in the book data table are linked to particular shelves, forming another one-to-many relationship.

The operation create_shelf creates a new shelf record and links it to a specific library record, while the operation create_book links a new book to a particular shelf.

For demonstration purposes, we invoke the create_shelf operation three times to create new shelves in different libraries, passing the required arguments:

chr tx --await create_shelf 1 "Biblioteca Apostolica Vaticana"
chr tx --await create_shelf 2 "Biblioteca Apostolica Vaticana"
chr tx --await create_shelf 1 "New York Public Library"

The successful invocation must return the following:

transaction with rid 0CAADC0937030F67C78003E2F9C1CA15E8C5D0C280ACA295CB8CD747AA09FBD9 was posted CONFIRMED

Then, we must add books to the newly created shelves using the create_book operation:

chr tx --await create_book "To Kill a Mockingbird" "New York Public Library" 1
chr tx --await create_book '"1984"' "Biblioteca Apostolica Vaticana" 1
chr tx --await create_book "Pride and Prejudice" "Biblioteca Apostolica Vaticana" 2
note

The number at the end of the create_book operation represents the shelf number specific in the create_shelf operation. To retrieve the shelf number, refer to the usage of the get_all_shelves query below.

Querying new records

To retrieve all existing shelves, you can use the get_all_shelves query:

chr query get_all_shelves

The result of this query is as follows:

[
  [
    "id": 5,
    "library_name": "Biblioteca Apostolica Vaticana",
    "number": 1
  ],
  [
    "id": 6,
    "library_name": "Biblioteca Apostolica Vaticana",
    "number": 2
  ],
  [
    "id": 7,
    "library_name": "New York Public Library",
    "number": 1
  ]
]

To retrieve all books, use the get_all_books query:

chr query get_all_books

This query returns the following:

[
  [
    "id": 9,
    "title": "To Kill a Mockingbird"
  ],
  [
    "id": 10,
    "title": "1984"
  ],
  [
    "id": 11,
    "title": "Pride and Prejudice"
  ]
]
tip

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