Relationships

Relationships define how collections connect to each other in TrackVision AI, enabling complex data structures while maintaining referential integrity. They allow you to build sophisticated data models that reflect real-world connections between different types of data.

Overview

Relationships in TrackVision AI are database connections between collections that enable you to structure data according to your business logic. They provide the foundation for creating normalized, efficient data models while offering powerful querying capabilities and user interface integration.

Each relationship type serves specific use cases:

• Many-to-One (M2O): Connect multiple items to a single parent
• One-to-Many (O2M): Access multiple related child items
• Many-to-Many (M2M): Connect items across collections with bidirectional relationships
• Many-to-Any (M2A): Flexible relationships across multiple collection types
• Translations: Specialized relationships for multi-language content

Relationship Types

Many-to-One (M2O)

Many-to-One relationships connect multiple items from one collection to a single item in another collection. This is implemented by adding a reference field in the "many" collection that points to the "one" collection.

Characteristics:

• Creates a physical database column
• Stores the ID of the related record
• Provides dropdown/selection interface for choosing related items
• Most common relationship type

Examples:

• Cities → Country: Many cities belong to one country
• Orders → Customer: Many orders can be placed by one customer
• Books → Publisher: Many books can be published by one publisher
• Articles → Author: Many articles can be written by one author
• Products → Category: Many products can belong to one category

Configuration:

{
  "collection": "articles",
  "field": "author",
  "type": "uuid",
  "related_collection": "authors",
  "display_template": "{{first_name}} {{last_name}}",
  "required": true
}

Database Schema:

-- Articles table gets a foreign key column
ALTER TABLE articles ADD COLUMN author UUID;
ALTER TABLE articles ADD FOREIGN KEY (author) REFERENCES authors(id);

One-to-Many (O2M)

One-to-Many relationships provide access to multiple related items from the "one" side of a Many-to-One relationship. This creates a virtual "Alias" field that doesn't correspond to a physical database column but provides an interface to access related items.

Characteristics:

• Virtual field (no database column created)
• Provides interface to manage related items
• Automatically created when M2O relationship exists
• Read-only in terms of database schema

Examples:

• Country → Cities: One country contains many cities
• Customer → Orders: One customer can have many orders
• Publisher → Books: One publisher can publish many books
• Author → Articles: One author can write many articles
• Category → Products: One category can contain many products

Configuration:

{
  "collection": "authors",
  "field": "articles",
  "type": "alias",
  "special": ["o2m"],
  "options": {
    "collection": "articles",
    "field": "author"
  }
}

Interface Benefits:

• Manage child records directly from parent record
• Inline editing of related items
• Drag-and-drop reordering (if sort field exists)
• Bulk operations on related items

Many-to-Many (M2M)

Many-to-Many relationships allow multiple items from one collection to relate to multiple items in another collection. This requires a junction collection to store the connections between the two collections.

Characteristics:

• Uses junction collection to store relationships
• Supports additional metadata in junction table
• Can be self-referencing (items relate to other items in same collection)
• Provides interface for selecting multiple related items

Examples:

• Students ↔ Courses: Students can take multiple courses, courses have multiple students
• Products ↔ Tags: Products can have multiple tags, tags apply to multiple products
• Users ↔ Roles: Users can have multiple roles, roles can be assigned to multiple users
• Related Articles: Articles can reference other articles

Configuration:

{
  "collection": "products",
  "field": "tags",
  "type": "alias",
  "special": ["m2m"],
  "options": {
    "collection": "tags",
    "junction_collection": "products_tags",
    "junction_field": "products_id",
    "one_field": "tags_id"
  }
}

Junction Table Structure:

{
  "collection": "products_tags",
  "fields": [
    {
      "field": "id",
      "type": "integer",
      "primary_key": true,
      "auto_increment": true
    },
    {
      "field": "products_id",
      "type": "uuid",
      "required": true
    },
    {
      "field": "tags_id",
      "type": "uuid",
      "required": true
    },
    {
      "field": "sort",
      "type": "integer"
    }
  ]
}

Self-Referencing Example:

{
  "collection": "articles",
  "field": "related_articles",
  "type": "alias",
  "special": ["m2m"],
  "options": {
    "collection": "articles",
    "junction_collection": "articles_related",
    "junction_field": "article_id",
    "one_field": "related_article_id"
  }
}

Many-to-Any (M2A)

Many-to-Any relationships allow one collection to relate to items across multiple different collections. This creates a flexible system where a single field can reference different types of content.

Characteristics:

• Junction collection stores both item ID and collection name
• Extremely flexible for content management
• Also known as "matrix" or "replicator" field
• Useful for creating modular, reusable content components

Examples:

• Page Blocks: Pages can contain various block types (text, image, video, gallery)
• Activity Feed: Activities can reference different types of content (posts, comments, likes)
• Notifications: Notifications can relate to various system objects
• Search Results: Results can come from multiple collection types

Configuration:

{
  "collection": "pages",
  "field": "blocks",
  "type": "alias",
  "special": ["m2a"],
  "options": {
    "collections": ["text_blocks", "image_blocks", "video_blocks"],
    "junction_collection": "pages_blocks",
    "junction_field": "pages_id",
    "one_collection_field": "collection",
    "one_allowed_collections": ["text_blocks", "image_blocks", "video_blocks"],
    "one_field": "item"
  }
}

Junction Table for M2A:

{
  "collection": "pages_blocks",
  "fields": [
    {
      "field": "id",
      "type": "integer",
      "primary_key": true,
      "auto_increment": true
    },
    {
      "field": "pages_id",
      "type": "uuid",
      "required": true
    },
    {
      "field": "collection",
      "type": "string",
      "required": true
    },
    {
      "field": "item",
      "type": "string",
      "required": true
    },
    {
      "field": "sort",
      "type": "integer"
    }
  ]
}

Translations

Translations create a specialized One-to-Many relationship for managing multi-language content. TrackVision AI automatically creates the necessary collections and relationships.

Characteristics:

• Automatically generates languages collection
• Creates junction collection for translations
• Provides interface for managing translations
• Supports fallback language hierarchy

Setup Process:

1. Enable translations on a collection
2. TrackVision AI creates languages collection (if not exists)
3. Creates junction collection (e.g., articles_translations)
4. Adds translation fields to junction collection

Generated Structure:

{
  "collection": "articles_translations",
  "fields": [
    {
      "field": "id",
      "type": "integer",
      "primary_key": true
    },
    {
      "field": "articles_id",
      "type": "uuid",
      "required": true
    },
    {
      "field": "languages_code",
      "type": "string",
      "required": true
    },
    {
      "field": "title",
      "type": "string"
    },
    {
      "field": "content",
      "type": "text"
    }
  ]
}

Languages Collection:

{
  "collection": "languages",
  "fields": [
    {
      "field": "code",
      "type": "string",
      "primary_key": true
    },
    {
      "field": "name",
      "type": "string"
    },
    {
      "field": "direction",
      "type": "string",
      "default_value": "ltr"
    }
  ]
}

Relationship Configuration

Basic Configuration Options

Display Templates

Control how related records appear in selection interfaces:

{
  "field": "author",
  "related_collection": "authors",
  "display_template": "{{first_name}} {{last_name}} - {{department.name}}"
}

Filter Conditions

Limit which related records can be selected:

{
  "field": "category",
  "related_collection": "categories",
  "filter": {
    "status": {
      "_eq": "active"
    },
    "type": {
      "_eq": "product_category"
    }
  }
}

Sort Options

Define default sorting for related records:

{
  "field": "tags",
  "related_collection": "tags",
  "sort": ["name", "created_at"]
}

Advanced Configuration

Cascade Behaviors

Control what happens when related records are deleted:

{
  "field": "category",
  "type": "uuid",
  "related_collection": "categories",
  "on_delete": "SET_NULL",  // Options: SET_NULL, CASCADE, RESTRICT, NO_ACTION
  "on_update": "CASCADE"
}

Interface Customization

Configure how relationships appear in the interface:

{
  "field": "author",
  "interface": "select-dropdown-m2o",
  "options": {
    "template": "{{first_name}} {{last_name}}",
    "enable_create": true,
    "enable_select_button": true
  }
}

Validation Rules

Add constraints to relationship fields:

{
  "field": "assigned_user",
  "type": "uuid",
  "related_collection": "users",
  "required": true,
  "validation": {
    "custom_rule": "must_be_active_user"
  }
}

Junction Table Management

Automatic Junction Tables

TrackVision AI automatically creates and manages junction tables for M2M relationships:

Naming Convention:

• Format: {collection1}_{collection2}
• Example: products_tags, users_roles, articles_categories

Standard Fields:

• id: Primary key
• {collection1}_id: Foreign key to first collection
• {collection2}_id: Foreign key to second collection
• sort: Optional field for manual ordering

Custom Junction Tables

Add additional metadata to junction tables:

{
  "collection": "project_team_members",
  "fields": [
    {
      "field": "project_id",
      "type": "uuid",
      "required": true
    },
    {
      "field": "user_id", 
      "type": "uuid",
      "required": true
    },
    {
      "field": "role",
      "type": "string",
      "required": true,
      "options": {
        "choices": [
          {"text": "Manager", "value": "manager"},
          {"text": "Developer", "value": "developer"},
          {"text": "Designer", "value": "designer"}
        ]
      }
    },
    {
      "field": "start_date",
      "type": "date",
      "required": true
    },
    {
      "field": "end_date",
      "type": "date"
    },
    {
      "field": "hourly_rate",
      "type": "decimal",
      "precision": 10,
      "scale": 2
    }
  ]
}

Querying Relationships

GraphQL Queries

Basic Relationship Query

query {
  articles {
    id
    title
    author {
      id
      first_name
      last_name
      email
    }
  }
}

Deep Relationship Query

query {
  articles {
    title
    author {
      name
      department {
        name
        company {
          name
          headquarters {
            city
            country
          }
        }
      }
    }
    tags {
      name
      category {
        name
      }
    }
  }
}

Many-to-Many with Junction Data

query {
  projects {
    name
    team_members {
      user {
        name
        email
      }
      role
      start_date
      hourly_rate
    }
  }
}

REST API Queries

Include Related Data

GET /items/articles?fields=*,author.first_name,author.last_name,tags.name

Filter by Related Data

GET /items/articles?filter[author][department][name][_eq]=Engineering

Sort by Related Data

GET /items/articles?sort=author.last_name,title

Aggregate Related Data

GET /items/authors?aggregate[count]=articles&aggregate[avg]=articles.rating

Advanced Query Patterns

Conditional Related Data

query {
  authors {
    name
    articles(filter: {status: {_eq: "published"}}) {
      title
      published_date
    }
  }
}

Paginated Relationships

query {
  categories {
    name
    products(limit: 10, offset: 0, sort: ["name"]) {
      name
      price
    }
  }
}

Performance Optimization

Database Indexing

Automatic Indexes

TrackVision AI automatically creates indexes for:

• Primary keys
• Foreign key fields
• Unique constraints

Custom Indexes

Add indexes for frequently queried relationships:

-- Composite index for junction table queries
CREATE INDEX idx_products_tags_lookup ON products_tags (products_id, tags_id);

-- Index for filtered relationship queries
CREATE INDEX idx_articles_author_status ON articles (author, status);

-- Index for sorted relationship displays
CREATE INDEX idx_products_category_name ON products (category, name);

Query Optimization

• Use field selection to limit data transfer
• Implement pagination for large relationship datasets
• Consider caching for frequently accessed relationships
• Monitor query performance and add indexes as needed

N+1 Query Prevention

GraphQL Optimization

TrackVision AI's GraphQL implementation automatically optimizes queries:

• Batches related record fetches
• Uses DataLoader pattern internally
• Reduces database round trips

REST API Optimization

Use field selection to fetch related data in single request:

# Good: Single query with related data
GET /items/articles?fields=*,author.*,tags.*

# Bad: Multiple queries (N+1 problem)
GET /items/articles
GET /items/authors/1
GET /items/authors/2
GET /items/tags/1

Caching Strategies

Relationship Caching

• Cache frequently accessed relationship data
• Use ETags for relationship change detection
• Implement cache invalidation on related record updates

Interface Caching

• Cache relationship dropdown options
• Store display templates in browser cache
• Use service workers for offline relationship data

Best Practices

Relationship Design

Data Modeling

1. Model Real-World Relationships: Reflect actual business relationships
2. Normalize Appropriately: Balance normalization with query complexity
3. Consider Cardinality: Choose appropriate relationship types
4. Plan for Scale: Design relationships that work with large datasets
5. Document Business Rules: Clearly define relationship constraints

Performance Considerations

1. Index Strategy: Index frequently queried relationship fields
2. Query Patterns: Design relationships for expected query patterns
3. Data Volume: Consider relationship complexity with large datasets
4. Cascade Behavior: Choose appropriate cascade settings for performance
5. Junction Table Design: Optimize junction tables for common queries

Data Integrity

Constraint Management

1. Referential Integrity: Use appropriate foreign key constraints
2. Cascade Settings: Choose cascade behavior carefully
3. Validation Rules: Implement business logic validation
4. Orphan Prevention: Plan for cleanup of disconnected data
5. Backup Strategy: Always backup before modifying relationships

Error Handling

1. Graceful Degradation: Handle missing related records
2. User Feedback: Provide clear error messages
3. Transaction Management: Use transactions for multi-table operations
4. Rollback Procedures: Plan for relationship change rollbacks
5. Audit Logging: Track relationship modifications

User Experience

Interface Design

1. Intuitive Selection: Make related record selection easy
2. Clear Display: Use meaningful display templates
3. Efficient Filtering: Provide relevant filter options
4. Logical Sorting: Sort related records meaningfully
5. Progressive Loading: Load relationships as needed

Performance UX

1. Loading States: Show loading indicators for relationship data
2. Pagination: Implement pagination for large relationship lists
3. Search Functionality: Enable search within relationships
4. Bulk Operations: Support bulk relationship management
5. Offline Support: Cache critical relationship data

Maintenance and Evolution

Schema Evolution

1. Version Control: Track relationship schema changes
2. Migration Planning: Plan for relationship modifications
3. Backward Compatibility: Maintain API compatibility when possible
4. Testing Strategy: Test relationship changes thoroughly
5. Documentation: Keep relationship documentation current

Monitoring and Optimization

1. Performance Monitoring: Track relationship query performance
2. Usage Analytics: Monitor relationship usage patterns
3. Regular Review: Periodically review relationship necessity
4. Index Optimization: Optimize indexes based on usage
5. Cleanup Procedures: Regular cleanup of orphaned relationship data

Common Patterns and Use Cases

Hierarchical Data Structures

Category Trees

{
  "collection": "categories",
  "fields": [
    {
      "field": "parent",
      "type": "uuid",
      "related_collection": "categories"
    },
    {
      "field": "children",
      "type": "alias",
      "special": ["o2m"],
      "options": {
        "collection": "categories",
        "field": "parent"
      }
    }
  ]
}

Organizational Hierarchies

{
  "collection": "departments",
  "fields": [
    {
      "field": "parent_department",
      "type": "uuid",
      "related_collection": "departments"
    },
    {
      "field": "manager",
      "type": "uuid",
      "related_collection": "users"
    }
  ]
}

Content Management Patterns

Polymorphic Content Blocks

{
  "collection": "page_blocks",
  "fields": [
    {
      "field": "page",
      "type": "uuid",
      "related_collection": "pages"
    },
    {
      "field": "collection",
      "type": "string"
    },
    {
      "field": "item",
      "type": "string"
    },
    {
      "field": "sort",
      "type": "integer"
    }
  ]
}

Content Tagging System

{
  "collection": "content_tags",
  "fields": [
    {
      "field": "content_type",
      "type": "string"
    },
    {
      "field": "content_id",
      "type": "uuid"
    },
    {
      "field": "tag",
      "type": "uuid",
      "related_collection": "tags"
    }
  ]
}

E-commerce Patterns

Product Variants

{
  "collection": "product_variants",
  "fields": [
    {
      "field": "product",
      "type": "uuid",
      "related_collection": "products"
    },
    {
      "field": "attributes",
      "type": "alias",
      "special": ["m2m"],
      "options": {
        "collection": "attributes",
        "junction_collection": "variant_attributes"
      }
    }
  ]
}

Order Management

{
  "collection": "order_items",
  "fields": [
    {
      "field": "order",
      "type": "uuid",
      "related_collection": "orders"
    },
    {
      "field": "product",
      "type": "uuid",
      "related_collection": "products"
    },
    {
      "field": "quantity",
      "type": "integer"
    },
    {
      "field": "unit_price",
      "type": "decimal"
    }
  ]
}

Audit and Versioning Patterns

Activity Logging

{
  "collection": "activity_log",
  "fields": [
    {
      "field": "user",
      "type": "uuid",
      "related_collection": "users",
      "on_delete": "NO_ACTION"
    },
    {
      "field": "collection",
      "type": "string"
    },
    {
      "field": "item",
      "type": "string"
    },
    {
      "field": "action",
      "type": "string"
    },
    {
      "field": "timestamp",
      "type": "timestamp"
    }
  ]
}

Content Versioning

{
  "collection": "content_revisions",
  "fields": [
    {
      "field": "item_collection",
      "type": "string"
    },
    {
      "field": "item_id",
      "type": "uuid"
    },
    {
      "field": "data",
      "type": "json"
    },
    {
      "field": "created_by",
      "type": "uuid",
      "related_collection": "users"
    }
  ]
}