Relations

> Connect tables with foreign keys to model real-world relationships.

Brickr's database supports relations between tables, allowing you to model how your data is connected -- users have orders, orders contain items, items belong to categories.

What are relations?

A relation links a column in one table to the unique row ID of another table. This is the standard relational database concept of a foreign key.

For example, if you have a users table and an orders table, you can add a user_id column to orders that references the row_id value in users.

One-to-many

A one-to-many relation means one record in table A can be linked to many records in table B.

Example: One user has many orders.

| users | | orders | |-------|-|--------| | row_id (unique) | <--- | user_id (FK) | | name | | product | | email | | total |

To set this up:

1. Open the orders table in Dashboard > Databases. 2. Add a column user_id with a relation to the users table. 3. When inserting an order, set the user_id to the corresponding user's row ID.

The relation column stores the row ID value of the related table. When you query orders, you get the user_id value and can use it to look up the corresponding user.

Many-to-many

A many-to-many relation means records in both tables can link to multiple records in the other. This requires a junction table.

Example: Students enroll in many courses. Courses have many students.

| students | | enrollments | | courses | |----------|-|-------------|-|---------| | row_id (unique) | <--- | student_id (FK) | | row_id (unique) | | name | | course_id (FK) | ---> | title |

To set this up:

1. Create a junction table (e.g., enrollments). 2. Add a student_id column with a relation to students. 3. Add a course_id column with a relation to courses. 4. Each row in the junction table represents one enrollment.

In your flow, you can query related data by chaining database nodes:

1. DB Find One -- Look up the parent record (e.g., a user by row ID). 2. DB Select -- Find all related records where the foreign key matches (e.g., all orders where user_id equals the user's ID).

For complex queries that join multiple tables, use the DB Query node with a SQL JOIN statement.

Best practices

  • Name foreign key columns clearly -- Use the pattern {related_table}_id (e.g., user_id, order_id).
  • Always set up relations through the schema editor -- This helps Brickr understand your data model.
  • Use find-one for lookups -- When you have a row ID and need the full record, use DB Find One rather than DB Select with a condition.
  • Avoid deeply nested relations -- If you find yourself chaining more than 2-3 lookups, consider using a raw SQL query with JOINs.

What's next?

| Topic | Description | |-------|-------------| | Operations | CRUD operations reference | | Tables | Column types and schema management |