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

> 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,…

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

Relationships in Rell explainedLesson 4 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/)

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
}
```

sql

```
create table "c0.shelf"(
  "rowid" bigint not null,
  "number" bigint not null,
  "library" bigint not null,
  constraint "PK_c0.shelf"
    primary key ("rowid"),
  constraint "c0.shelf_library_FK"
    foreign key ("library")
    references "c0.library" ("rowid")
);
create index "IDX_c0.shelf_0" on "c0.shelf"("library");

create table "c0.book"(
  "rowid" bigint not null,
  "title" text not null,
  "shelf" bigint not null,
  constraint "PK_c0.book"
    primary key ("rowid"),
  constraint "c0.book_shelf_FK"
    foreign key ("shelf")
    references "c0.shelf" ("rowid")
);
create index "IDX_c0.book_0" on "c0.book"("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](https://docs.chromia.com/rell/language-features/modules/entity#unique-keys-and-indices) 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

```nolines
chr test
```

A passing run produces this output:

shell

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

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

```nolines
chr node start
```

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

shell

```nolines
chr node update
```

See the [Chromia CLI node documentation](https://docs.chromia.com/build/cli/commands/node/) 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

```nolines
chr tx --await create_shelf 1 "Biblioteca Apostolica Vaticana"
```

shell

```nolines
chr tx --await create_shelf 2 "Biblioteca Apostolica Vaticana"
```

shell

```nolines
chr tx --await create_shelf 1 "New York Public Library"
```

Each successful call returns:

shell

```nolines
transaction with rid 0CAADC0937030F67C78003E2F9C1CA15E8C5D0C280ACA295CB8CD747AA09FBD9 was posted CONFIRMED
```

Next, place books on the new shelves with `create_book`:

shell

```nolines
chr tx --await create_book "To Kill a Mockingbird" "New York Public Library" 1
```

shell

```nolines
chr tx --await create_book '"1984"' "Biblioteca Apostolica Vaticana" 1
```

shell

```nolines
chr tx --await create_book "Pride and Prejudice" "Biblioteca Apostolica Vaticana" 2
```

note

The final number passed to `create_book` is the shelf number you specified in the `create_shelf` operation. To look up shelf numbers, use the `get_all_shelves` query shown below.

## Querying new records

Run the `get_all_shelves` query to list every shelf:

shell

```nolines
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

```nolines
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"
  ]
]
```

tip

You can also try the other queries defined earlier in this lesson to test your setup.

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

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