How to create self-reference database table?

Designing a database for applications is rarely complete without creating relationships between tables. The AppMaster database editor allows you to make such a relationship in one simple move. You just need to draw a link line between tables and then select the type of relationship (one-to-one, one-to-many, many-to-many) and, if necessary, the names of the fields. But what if the table must refer to itself?

Let's analyze the situation when it is necessary to maintain a certain log of records. At the same time, it is organized in such a way that records are created strictly in a chain. Each entry must be linked to the previous and subsequent entry (except for the very first and last entry, of course). Thus, the entire chain can be tracked for each individual record, and the system receives additional protection against unauthorized changes.

Database design

First, we need to create the model itself in the database designer. Let's name it Journal and add one text field, record.

The next step is to create a connection. To do this, right-click anywhere on the canvas and select Create relation.

The next step is to set up the connection. To do this, you need to select the same model as Source and Target (in this case, Journal), set the field names (next and previous), and the type of connection (has_one).

The self-referencing table setup is complete. You can start creating the front-end design and business processes.

Creating business process

Let's create a backend business process to add new entries to the journal. It will receive the text of the new entry as an input parameter. This will search the database for the latest entry to associate it with the new entry. For searching, you can set _Limit = 1 (because only one last record is needed), which will be enough since, by default, the records will be ordered by ID and issued in the order they were added to the database, starting with the last record.

The result of the search will be an array consisting of one element. We need to extract it using the Array Element block with index 0 and form a new journal entry (Make Journal). The last step is just to write to the database (DB: Create Journal) and complete the business process.

This business process can be used for the default endpoint to create new database records.

Front-end design

For the front-end of a web application, it is enough to add three elements to the page.

1. Text field for entering the content of the new entry
2. Button to add an entry
3. Table to display all entries.
   

For the "ADD Journal Record" button, you also need to create a simple business process. On clicking the button, the text from the input field should be sent to the endpoint to create new records, and another button to be clicked to update the contents of the table as the last action.

For the table itself, it is also worth setting up business processes, but in this lesson, we will not dwell on this. All the necessary information can be found in the lesson on setting up tables. It is only worth noting that when getting data, you need to specify the _with the parameter to get data from related tables (despite the fact that, in our case, the table is related to itself).

We can publish the application, add a few entries, and verify that everything works correctly. Each entry is really related to entries in the same table. For example, for an entry with ID 3, there is a link to the previous entry (ID 2) and the next entry (ID 4).