# 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](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/advanced-fields/linked-table-field):

```
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](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/advanced-fields/linked-table-field)
* 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](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/advanced-fields/linked-table-field) 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](https://docs.buzzy.buzz/rest-api/buzzy-rest-api/rest-api) - Full CRUD operations for external integrations
* [Async API Documentation](https://docs.buzzy.buzz/the-building-blocks/code-widget-custom-code/new-async-api-+-react-html-components) - 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](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/security-and-access-control) for detailed information.

## Real-World Examples

### Chat Application

Based on our [AI-Powered Chat App](https://docs.buzzy.buzz/working-with-buzzy/buzzy-app-examples/buzzy-ai-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](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/buzzy-datamodel-overview) - Complete guide to Buzzy's datamodel
* [Linked Table Fields](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/advanced-fields/linked-table-field) - Many-to-many relationships
* [Datatable to Datatable Relationships](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/datatable-to-datatable-relationships) - All relationship types
* [Security and Access Control](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/security-and-access-control) - Protecting your data
* [REST API](https://docs.buzzy.buzz/rest-api/buzzy-rest-api/rest-api) - Programmatic data access
* [Async API](https://docs.buzzy.buzz/the-building-blocks/code-widget-custom-code/new-async-api-+-react-html-components) - Code Widget integration
* [App Examples](https://docs.buzzy.buzz/working-with-buzzy/buzzy-app-examples) - Real-world implementations

***