Chromia
Start Building

04 / 06

One-to-many relationships

A one-to-many relationship links one record in a table to several records in another. In a library system, for example, one library holds many shelves, while every shelf belongs to exactly one library. You usually model this with a foreign key in the child table, which is the table on the "many" side.

To see how this works in SQL, consider two such relationships: Library to Shelf, and Shelf to Book. The SQL definitions look like this:

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

What each table does:

  • Library table: Stores the library data.
  • Shelf table: Uses the foreign key library_id to tie every shelf to one library.
  • Book table: Uses the foreign key shelf_id to tie every book to one shelf.

This approach works, but you have to manage foreign keys and constraints yourself to keep the data consistent, and every relationship depends on precise SQL syntax. Rell lets you express the same entities and relationships more directly. It hides most of that complexity, so the resulting code is shorter and easier to read.

Put the following code in src/library_management/entities.rell:

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}

You now need two operations: one that creates a shelf inside a given library, and one that creates a book and places it on a given shelf. Add this code to operations.rell 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);}

How the code models the relationships:

  • Library and shelf: A library can hold many shelves. The library attribute in the shelf entity (indexed) sets up this relationship.
  • Shelf and book: A shelf can hold many books. The shelf attribute in the book entity (indexed) sets up this relationship.

An index in Rell makes it fast to query entities by particular attributes. In short, an index points to data in a table. When you index certain attributes, queries that filter or fetch entities by those attributes run much faster, because the index leads straight to the matching data.

To check your work, add the following queries for reading records to queries.rell:

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

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

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

shell
chr test

A passing run produces this output:

shell
====================Running unit tests====================TEST: test.my_library_test:test_create_libraryOK: test.my_library_test:test_create_library (1.771s)------------------------------------------------------------TEST RESULTS:OK test.my_library_test:test_create_librarySUMMARY: 0 FAILED / 1 PASSED / 1 TOTAL (1.771s)***** OK *****

Creating Records

You need a local Chromia node to work with the database. Start it without the --wipe flag so the records from the previous lesson are kept; the queries below depend on them.

shell
chr node start

If the node is already running, apply the changes with this command instead:

shell
chr node update

See the Chromia CLI node documentation for details.

This example uses two data tables, shelf and book, to form one-to-many relationships. Each record in shelf points to one library record, and each record in book points to one shelf.

create_shelf adds a shelf record and attaches it to a library record. create_book adds a book and attaches it to a shelf.

Call create_shelf three times with the required arguments to add shelves to different libraries:

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

Each successful call returns:

shell
transaction with rid 0CAADC0937030F67C78003E2F9C1CA15E8C5D0C280ACA295CB8CD747AA09FBD9 was posted CONFIRMED

Next, place books on the new shelves with create_book:

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

Querying new records

Run the get_all_shelves query to list every shelf:

shell
chr query get_all_shelves

It returns:

json
[  [    "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  ]]

Run the get_all_books query to list every book:

shell
chr query get_all_books

It returns:

json
[  [    "id": 9,    "title": "To Kill a Mockingbird"  ],  [    "id": 10,    "title": "1984"  ],  [    "id": 11,    "title": "Pride and Prejudice"  ]]
Written for this site from Chromia’s public course. Code samples are the course’s own.