Relationships

Discover How to Access Related Data Across Linked Tables

Open a Table

1. Begin by selecting a database connection from the DB Explorer.

2. Choose the desired database, then select a table by clicking it.

   

Identify Linked Relationships

To understand the relationships between tables, hover over a cell containing a Primary Key or Foreign Key. A small relationship icon will appear beside the cell, indicating that the column is linked to another table.

Browsing Foreign Key Relationships

Foreign key relationships allow you to navigate data across multiple tables in both directions:

• From Foreign Key to Primary Key: When you click on the relationship icon next to a foreign key, you can view the related records in the primary key table. For example, clicking on a CustomerID in an Orders table will show you all orders related to that specific customer in the Customers table.

• From Primary Key to Foreign Key: Conversely, while in the related table (e.g., Customers), you can explore how many orders each customer has by following the foreign key back to the Orders table.

View Filtered Related Data

To see the linked data:

1. Hover over the relationship icon to view the message displaying the filtering query for the related table.

2. Click the relationship icon to instantly filter and display data from the related table, as shown below.

   

This two-way navigation of foreign key relationships enhances your ability to analyze data comprehensively, allowing for deeper insights and better data integrity.

Inferred Relationships

Some databases like MongoDB, DynamoDB, Cassandra, and others don’t have native foreign key support. DBCode can automatically detect relationships based on column naming conventions, enabling the same relationship browsing experience.

How It Works

Inferred relationships use pattern matching to detect columns that reference other tables. For example:

• A column named user_id in an orders table likely references the id column in a users table
• A column named customerId references id in a customers table

Configuring Inferred Relationships

You can configure inferred relationship patterns in two ways:

1. Connection Settings

1. Open your connection settings (right-click connection → Edit Connection)
2. Navigate to the Inferred Relationships section
3. Enable the patterns you want to use:
   • Suffix patterns: Match columns ending with _id, Id, _fk, etc.
   • Custom patterns: Define your own regex patterns for specific naming conventions
4. Set the Target column (default: id) - the column name to reference in target tables

2. Entity Relationship Diagram (ERD)

For visual configuration:

1. Open the ERD diagram for your database (right-click tables → Open Diagram)
2. Click the Inferred Relationships tool in the toolbar
3. Toggle patterns on/off and see relationships update in real-time
4. Click Save to persist your configuration to the connection

Predefined Patterns

DBCode includes several predefined patterns:

Pattern	Description	Example
column_id → column.id	Underscore suffix	user_id → users.id
columnId → column.id	CamelCase suffix	userId → users.id
column_fk → column.id	Foreign key suffix	user_fk → users.id
fk_column → column.id	Foreign key prefix	fk_user → users.id

Custom Patterns

For databases with unique naming conventions, you can create custom regex patterns:

• Column Pattern: Regex to match the source column name (use capture group for table name)
• Table Pattern: Template for the target table name (use $1 for captured group)
• Target Column: The column to reference in the target table

Example: To match ref_users_key → users.pk:

• Column Pattern: ref_(.+)_key
• Table Pattern: $1
• Target Column: pk

Explicit Mappings

For relationships that don’t follow any naming pattern, you can define explicit column-to-column mappings. This is useful when:

• Column names don’t match any pattern (e.g., creator → users.id)
• You want to override pattern-detected relationships

To add an explicit mapping:

1. In Connection Settings or ERD tool panel, find the Explicit Mappings section
2. Click Add Mapping
3. Enter the source and target in the format table.column or schema.table.column:
   • From: The source column (e.g., orders.creator)
   • To: The target column (e.g., users.id)

Examples:

From	To	Description
orders.creator	users.id	Non-standard column name
logs.entity_ref	products._id	Generic reference column
audit.modified_by	auth.users.id	Cross-schema relationship

MongoDB Example

For MongoDB collections with document references:

// orders collection
{
  _id: ObjectId("..."),
  user_id: ObjectId("..."),  // References users._id
  items: [...]
}

// users collection
{
  _id: ObjectId("..."),
  name: "John"
}

Configure an inferred relationship pattern:

1. Enable the column_id → column.id pattern
2. Set Target column to _id (MongoDB’s default primary key)

Now when viewing orders, you’ll see the relationship icon on user_id cells, allowing you to browse to the related user document.