Stop Using Join Tables: Managing Relationships with SurrealDB Record Links

The Join Table Tax

In traditional relational databases, creating a many-to-many relationship requires a join table—a middleman that exists solely to map IDs from two different tables. As your data model grows, these tables clutter your schema and force you to write increasingly complex JOIN statements that degrade performance and readability.

The takeaway is simple: you can replace these bridge tables with Record Links and Graph Edges. This allows you to treat relationships as direct pointers or first-class entities, reducing the cognitive load of your queries and the physical overhead of your storage.

Direct Linking vs. Graph Edges

SurrealDB offers two primary ways to connect data. Choosing the right one depends on whether the relationship itself carries information.

Record Links (Direct Pointers)

A record link is essentially a pointer to another record's unique ID. Instead of storing an integer user_id, you store a reference like user:john. This is ideal for simple one-to-many or many-to-one relationships where the connection is a binary fact (e.g., a post belongs to a user).

Graph Edges (The RELATE Statement)

When the relationship has its own properties—such as the date a user followed another user, or the role a member has in a project—you use a Graph Edge. In SurrealDB, this is handled via the RELATE statement. This transforms the connection into a first-class record that can hold its own metadata, effectively turning your database into a graph.

Practical Implementation: A Permission System

Consider a scenario where users are assigned to projects with specific roles (e.g., 'Admin', 'Editor', 'Viewer'). In SQL, this requires a user_projects table. In SurrealDB, we use a graph edge.

Step 1: Define the Relationship

Run these commands in the SurrealQL editor or via the CLI. Ensure you are connected to your instance with administrative permissions.

-- Create the entities
CREATE user:alice SET name = 'Alice';
CREATE project:alpha SET name = 'Project Alpha';

-- Create a relationship with metadata
RELATE user:alice -> assigned_to -> project:alpha 
SET role = 'Admin', joined_at = time::now();

Step 2: Traverse the Graph

To find all projects Alice is assigned to, and her role in each, you use the traversal operator (->). This avoids the need for a JOIN clause.

-- Fetch projects Alice is assigned to, including the role on the edge
SELECT ->assigned_to->project.*, ->assigned_to.role AS role 
FROM user:alice;

Expected Result: The query returns the project details and the 'Admin' role associated with the edge connecting Alice to Project Alpha.

Trade-offs and Performance Limits

While removing join tables simplifies the schema, graph traversals are not free. The primary risk is deep nesting. If you chain multiple traversals (e.g., user->assigned_to->project->belongs_to->organization->location), the database must perform multiple lookups.

To maintain performance, keep these constraints in mind:

• Indexing: Ensure that any fields used for filtering during a traversal are indexed.
• Schema Mode: In SCHEMALESS mode, you have maximum flexibility, but you lose the ability to enforce that a link actually points to a valid record. Use SCHEMAFULL for production environments to ensure referential integrity.
• Traversal Depth: Limit the number of hops in a single query. If you find yourself traversing more than three levels deep frequently, reconsider your data partitioning.

Verifying Your Model

To verify that your relationships are functioning as intended, run a reverse traversal. If you created a link from user to project, check if you can find the user from the project side using the <- operator:

-- Find all users assigned to Project Alpha
SELECT <-assigned_to<-user.* FROM project:alpha;

If this returns the expected users, your graph edges are correctly bidirectional and indexed.