Record links

The second relationship type we’ll be exploring is record links. We’ll go through:

• How record links work
• How to practically model our data using record links
• How to use record links in our CRUD operations

How record links work

A record link is simply an external record ID that is directly embedded in another record, similar to a foreign key in relational databases.

However, record links are different from foreign keys because they act as a record pointer, pointing directly to record IDs on the underlying Key-Value storage engine.

This allows us to traverse from record to record without needing to run a table scan query because the query planner knows exactly what table and record the ID belongs to.

Therefore, it directly fetches the record instead of scanning for a matching needle in a haystack, like in traditional SQL joins.

So, there is no special statement for record links since we insert record IDs into other records to link them together.

Modelling our data using record links

In the previous lesson, we created a primary relationship based on the major actions a person using our e-commerce store would take: wishlist, cart, order and review.

Now we are moving on to secondary relationships. Which are:

• Making a one-way relationship from the product to the seller.
• Making bidirectional relationships to and from address_history and payment_details to the person.

One important thing to note before moving on, is that while we’re talking about primary and secondary relationships here, you can model your data completely using graph relations or using record links.

It all depends on your needs, this is just one recommended way of doing things.

One-way record links

UPDATE product SET seller = seller:surrealdb;

To make a one-way relationship from the product to the seller, we can embed the record ID seller:surrealdb into the seller field for all the records in our product table.

That way, we can link every product to information about the seller of that product. Since the Surreal Deal Store is based on our swag store, SurrealDB.store, the seller will always be SurrealDB. Which is why we’ve created this one-to-many relationship.

An important thing to notice here is that we haven’t created the seller table yet, but we are still able to insert the seller record ID into the product table because there are no foreign key constraints for record links.

CREATE seller:surrealdb
SET name = "SurrealDB", email = "education@surrealdb.com"

Let’s therefore quickly create the seller table such that our product record link will actually go somewhere.

Bidirectional record links

We can now make bidirectional relationships to and from address_history and payment_details to the person.

CREATE address_history:ulid(), payment_details:ulid()
SET person = person:01GFFXDCG89SAR3WM2SDV2E1RA
PARALLEL;

Starting with inserting a person record ID into both tables. We can now query in one direction, from address_history and payment_details to person.

UPDATE person:01GFFXDCG89SAR3WM2SDV2E1RA MERGE {
	address_history: address_history:01HCWCEB1R8Y499XJYPVWE04RX,
	payment_details: payment_details:01FTZ3MR7095CRDT1A04NNRQ6H
};

To make this bidirectional, we need to insert the address_history and payment_details record IDs into the person table as well.

An important thing to note is that this will only create a link between these individual records, not every record in each table because we have specified the IDs

UPDATE product SET seller = seller:surrealdb;

To link every record together, we’d need to not specify any record ID in the person table, this will add the link to all records in the table. Like we did in the previous example

Record links: CRUD operations

We’ve already covered how to create record links, let’s now explore how to use them in the rest of our CRUD operations.

-- Selecting the current address from the person table
SELECT
    person.address.address_line_1  AS current_address,
    addresses[0].address_line_1 AS previous_address
FROM address_history:01HCWCEB1R8Y499XJYPVWE04RX;

-- Adding the current address to the address_history table
UPDATE address_history:01HCWCEB1R8Y499XJYPVWE04RX
SET addresses += person.address;

-- Deleting the address history from Leoma
DELETE address_history
WHERE person.name = "Leoma Santiago"

As these examples show, you can use record links in CRUD operations in the same way as you would, if it was all embedded in the same record, using the dot and bracket notation.

Summary

Now that we’ve interlinked our knowledge of record links, let’s summarise what we’ve learned.

Record links:

• Are external record IDs that are directly embedded in other records, similar to foreign keys in relational databases, but allowing us to traverse from record to record without needing to run a table scan query.
• Don’t need any statement, we just insert record IDs into other records to link them together.
• By default, record links only work in one direction: from the record where it’s embedded and to the record it points to.
• Need to be embedded in both tables to work bi-directionally, as we did with the address_history and payment_details tables.

You can use record links in CRUD operations in the same way as you would, if it was all embedded in the same record, using the dot and bracket notation.

That’s everything about record links, I’ll see you in the next lesson, where we’ll explore relational style joins.