Datatable to Datatable Relationships

Relationships allow you to connect datatables together, creating powerful data models that reflect real-world associations. When users view records, they can automatically see related data from connected datatables. This guide shows you how to create and work with different types of relationships in Buzzy.

Overview

Buzzy supports several types of relationships between datatables:

  1. One-to-Many (1:M): One parent record has multiple child records

  2. Many-to-Many (N:M): Multiple records in one datatable relate to multiple records in another

  3. Hierarchical: Multi-level parent-child relationships

For a comprehensive overview of all relationship types, see our Buzzy Datamodel Overview.

Relationship Types

1. Sub-table Relationships (1:M)

Sub-tables create one-to-many relationships where child records are automatically linked to their parent via embeddingRowID.

Parent Datatable (e.g., Projects)
├── _id: "proj_001"
├── Name: "Website Redesign"
└── Child Records (Sub-table)
    ├── Task 1
    │   ├── _id: "task_001"
    │   ├── embeddingRowID: "proj_001" ← Automatic link to parent
    │   └── Title: "Design Homepage"
    └── Task 2
        ├── _id: "task_002"
        ├── embeddingRowID: "proj_001" ← Automatic link to parent
        └── Title: "Develop Backend"

Setup: Add a Sub-table field to the parent datatable pointing to the child datatable.

2. Linked Table Field Relationships (N:M)

Linked Table Fields create many-to-many relationships by referencing records in another datatable.

Tasks ←→ Users (Many-to-Many)

Task 1
├── _id: "task_001"
├── Title: "Design Homepage"
└── Assignee (Linked Table Field): "user_123" ← References Users datatable

Task 2
├── _id: "task_002"
├── Title: "Develop Backend"
└── Assignee (Linked Table Field): "user_123" ← Same user, different task

User
├── _id: "user_123"
├── Name: "Alice Designer"
└── Email: "[email protected]"

Setup: Add a Linked Table Field to reference records from another datatable.

Practical Tutorial: Location Hierarchy

Let's create a practical example with a location-based hierarchy system.

Step 1: Create the Parent Datatable (Locations)

  1. Create a new datatable named "Locations"

  2. Set "Who can View Rows" to enable appropriate viewers

  3. Add these fields:

    • TEXT Field: "Place Name"

    • LOCATION Field: "Address"

Step 2: Create the Child Datatable (Items)

  1. Create a new datatable named "Items"

  2. Add these fields:

    • TEXT Field: "Description"

    • LINKED TABLE FIELD: "Parent Location"

      • Configure to reference: Locations datatable, Place Name field

Step 3: Configure Sorting and Filtering

  1. In Items datatable properties:

    • Set Sort Field 1 to "Parent Location"

  2. In Locations datatable properties:

    • Set Sort Field 1 to "Place Name"

  3. In Locations datatable Advanced Properties, add this rule:

    When row is filtered, then target Datatable ID: Items, broadcast Field ID: Place Name

Step 4: Test the Relationship

  1. Add sample locations:

    • Place Name: "Downtown Office", Address: "123 Main St, City"

    • Place Name: "Warehouse", Address: "456 Industrial Blvd, City"

  2. Add sample items:

    • Description: "Conference Room Equipment", Parent Location: "Downtown Office"

    • Description: "Office Supplies", Parent Location: "Downtown Office"

    • Description: "Inventory Storage", Parent Location: "Warehouse"

  3. Test filtering:

    • Click the filter button on a location row

    • The Items datatable should automatically filter to show only items for that location

Advanced Relationship Patterns

Multi-Level Hierarchies

You can create complex hierarchies by chaining relationships:

Organization
└── Departments (Sub-table)
    └── Projects (Sub-table)
        └── Tasks (Sub-table)
            └── Assignee (Linked Table Field) → Users

Cross-Reference Relationships

Create many-to-many relationships with junction datatables:

Students ←→ Enrollments ←→ Courses

Student
├── _id: "student_001"
└── Name: "John Doe"

Enrollment (Junction)
├── _id: "enroll_001"
├── Student (Linked Table Field): "student_001"
├── Course (Linked Table Field): "course_001"
└── Grade: "A"

Course
├── _id: "course_001"
└── Title: "Introduction to Programming"

Displaying Related Data

When creating views and screens, you can display:

  1. Child data: Use sub-table fields to show related child records

  2. Linked data: Include fields from linked datatables

  3. Parent data: Reference parent fields for breadcrumb navigation

Example: In a Task view, you can show:

  • Task details (current record)

  • Project name (parent via embeddingRowID)

  • Organization name (grandparent)

  • Assignee details (linked table data)

Working with Relationships via API

REST API Examples

// Get all child records for a parent
GET /api/microappdata?microappId=tasks&embeddingRowID=proj_001

// Create a child record with automatic parent linking
POST /api/insertmicroapprow
{
  "microappId": "tasks",
  "data": {
    "embeddingRowID": "proj_001",
    "title": "New Task",
    "assigneeId": "user_123"
  }
}

Async API Examples

// Get related records in a Code Widget
const childTasks = await buzzyAPI.getMicroAppData({
  microappId: 'tasks',
  embeddingRowID: currentProject._id
});

// Create related record
await buzzyAPI.insertMicroAppRow({
  microappId: 'tasks',
  data: {
    embeddingRowID: currentProject._id,
    title: 'New Task',
    assigneeId: selectedUser._id
  }
});

Best Practices

  1. Plan your relationships: Design your datamodel before implementation

  2. Use consistent naming: Follow clear conventions for relationship fields

  3. Test performance: Verify performance with realistic data volumes

  4. Document relationships: Keep track of how datatables connect

  5. Consider security: Use security controls to protect related data

Related Documentation

PreviousFormulasNextSecurity and Access Control

Last updated