Sub tables

Sub-table fields create one-to-many (1:M) relationships between datatables, where one parent record can have multiple child records. This is one of the most powerful features in Buzzy's datamodel, allowing you to build complex hierarchical data structures.

Overview

A sub-table field automatically manages the relationship between parent and child records using the embeddingRowID system. When you add a sub-table field to a datatable, child records are automatically linked to their parent record.

Parent Record (e.g., Restaurant Receipt)
├── _id: "receipt_001"
├── Restaurant: "Pizza Palace"
├── Date: "2024-07-03"
└── Items (Sub-table field)
    ├── Item 1
    │   ├── _id: "item_001"
    │   ├── embeddingRowID: "receipt_001" ← Automatic link to parent
    │   ├── Name: "Margherita Pizza"
    │   └── Price: 18.99
    └── Item 2
        ├── _id: "item_002"
        ├── embeddingRowID: "receipt_001" ← Automatic link to parent
        ├── Name: "Caesar Salad"
        └── Price: 12.50

Common Use Cases

1. Invoice and Invoice Lines

Invoice
├── _id: "inv_001"
├── Invoice Number: "INV-2024-001"
├── Customer: "Acme Corp"
└── Invoice Lines (Sub-table)
    ├── Line 1: Web Development - $1,500
    ├── Line 2: Design Services - $800
    └── Line 3: Hosting Setup - $200

2. Project and Tasks

Project
├── _id: "proj_001"
├── Name: "Website Redesign"
├── Status: "In Progress"
└── Tasks (Sub-table)
    ├── Task 1: Design Homepage
    ├── Task 2: Develop Backend
    └── Task 3: User Testing

3. Order and Order Items

Order
├── _id: "order_001"
├── Customer: "John Doe"
├── Order Date: "2024-07-03"
└── Order Items (Sub-table)
    ├── Item 1: Laptop - Qty: 1
    ├── Item 2: Mouse - Qty: 2
    └── Item 3: Keyboard - Qty: 1

Multi-Level Hierarchies

You can create complex multi-level relationships by chaining sub-tables:

Organization (Level 1)
├── _id: "org_001"
├── Name: "Tech Solutions Inc"
└── Departments (Sub-table - Level 2)
    ├── Department 1
    │   ├── _id: "dept_001"
    │   ├── embeddingRowID: "org_001"
    │   ├── Name: "Engineering"
    │   └── Projects (Sub-table - Level 3)
    │       ├── Project 1
    │       │   ├── _id: "proj_001"
    │       │   ├── embeddingRowID: "dept_001"
    │       │   ├── Name: "Mobile App"
    │       │   └── Tasks (Sub-table - Level 4)
    │       │       ├── Task 1: UI Design
    │       │       └── Task 2: Backend API
    │       └── Project 2
    │           └── Tasks (Sub-table)
    └── Department 2
        └── Projects (Sub-table)

Combining Sub-tables with Linked Table Fields

Sub-tables become even more powerful when combined with Linked Table Fields:

Invoice
└── Invoice Lines (Sub-table)
    ├── Line 1
    │   ├── embeddingRowID: "inv_001"
    │   ├── Quantity: 2
    │   ├── Product (Linked Table Field): "prod_123" ← References Products datatable
    │   └── Unit Price: $99.99
    └── Line 2
        ├── embeddingRowID: "inv_001"
        ├── Quantity: 1
        ├── Product (Linked Table Field): "prod_456" ← References Products datatable
        └── Unit Price: $149.99

Products Datatable (Referenced by Linked Table Fields)
├── Product 1
│   ├── _id: "prod_123"
│   ├── Name: "Premium Widget"
│   ├── Category: "Electronics"
│   └── Description: "High-quality widget"
└── Product 2
    ├── _id: "prod_456"
    ├── Name: "Deluxe Widget"
    ├── Category: "Electronics"
    └── Description: "Top-tier widget"

Displaying Related Data

When displaying records that use sub-tables, you can show data from multiple levels:

Current Row Data

• Fields from the current record being displayed

Child Data (Sub-table)

• Records from child datatables via sub-table fields

• Automatically filtered to show only children of the current record

Linked Data

• Data from datatables referenced by Linked Table Fields

• Product names, user details, category information, etc.

Parent/Ancestor Data

• Data from parent records (via embeddingRowID)

• Grandparent and great-grandparent data for breadcrumb navigation

Example: When displaying an Invoice Line, you can show:

• Line details (current record)

• Product name and image (linked table data)

• Invoice number and customer (parent data)

• Organization name (grandparent data)

Performance Considerations

Sub-tables are powerful but require careful consideration for performance:

Best Practices

1. Limit nesting levels: While multiple levels are supported, test performance with realistic data volumes

2. Use filtering: Apply filters to sub-table views to limit data retrieval

3. Consider data volume: Large numbers of child records may impact performance

4. Test with real data: Always test with production-like data volumes

5. Monitor query performance: Watch for slow-loading screens with complex hierarchies

When to Consider Alternatives

• Very large datasets: Consider pagination or alternative data structures

• Frequently accessed data: Cache commonly used data

• Complex queries: Consider flattening some relationships for performance

Infrastructure Scaling

For high-performance requirements:

• Upgrade your Buzzy deployment for more powerful infrastructure

• Consider database optimization for large datasets

• Implement caching strategies for frequently accessed data

Setup Instructions

1. Create the Parent Datatable

• Design your parent datatable with appropriate fields

• Consider what child data will be related

2. Create the Child Datatable

• Create the child datatable with its own fields

• Include any Linked Table Fields for additional relationships

3. Add Sub-table Field to Parent

• In the parent datatable, add a new field

• Select "Sub-table" as the field type

• Configure the target datatable (child datatable)

• Set appropriate permissions and display options

4. Configure Display Options

• Set how child records should be displayed

• Configure sorting and filtering for the sub-table

• Set permissions for who can add/edit child records

Working with Sub-tables Programmatically

For detailed API examples and implementation guides for working with sub-tables programmatically, see:

• REST API Documentation - Full CRUD operations for external integrations

• Async API Documentation - Client-side data operations within Code Widgets

Security and Access Control

Sub-tables inherit security settings but can have additional controls:

• Parent-level security: Controls access to the parent record and all children

• Child-level security: Additional controls on individual child records

• Field-level permissions: Control who can edit specific fields in child records

See Security and Access Control for detailed information.

Real-World Examples

Chat Application

Based on our AI-Powered Chat App:

Chat
├── Title: "Project Discussion"
└── Messages (Sub-table)
    ├── Message 1: "Let's discuss the timeline"
    ├── Message 2: "Sounds good!"
    └── Message 3: "When can we start?"

Project Management

Project
├── Name: "Website Redesign"
├── Status: "Active"
└── Tasks (Sub-table)
    ├── Task 1
    │   ├── Title: "Design Homepage"
    │   ├── Assignee (Linked Table Field): User A
    │   └── Status: "In Progress"
    └── Task 2
        ├── Title: "Develop Backend"
        ├── Assignee (Linked Table Field): User B
        └── Status: "Not Started"

Related Documentation

• Buzzy Datamodel Overview - Complete guide to Buzzy's datamodel

• Linked Table Fields - Many-to-many relationships

• Datatable to Datatable Relationships - All relationship types

• Security and Access Control - Protecting your data

• REST API - Programmatic data access

• Async API - Code Widget integration

• App Examples - Real-world implementations

---