Connection fields are the most powerful field type in Tadabase. They enable you to create relationships between tables, linking related data together. Instead of duplicating information across multiple tables, connection fields let you reference data from other tables, creating sophisticated, normalized database structures.

In this lesson, you'll learn what connection fields are, how to create relationships between tables, understand one-to-many and many-to-many relationships, build parent-child structures, configure connection settings, and use connected field values. Mastering connections is essential for building professional, scalable applications.

A connection field links records in one table to records in another table. Think of it like creating a relationship between two entities in the real world.

For example:

• A Customer has many Orders
• An Order belongs to one Customer
• A Student enrolls in many Courses
• A Course has many Students
• A Project has many Tasks
• A Task belongs to one Project

Connection fields provide several benefits:

• No Data Duplication - Store information once, reference it everywhere
• Data Consistency - Changes in one place update everywhere
• Easier Maintenance - Update one record instead of many
• Better Organization - Clear relationships between data
• Advanced Features - Aggregations, roll-ups, cascade actions
• Scalability - Handle complex data structures efficiently

Bad Approach (Duplication):

Orders table with customer information duplicated:

• Order 1: Customer Name: John Smith, Email: john@example.com, Phone: 555-1234
• Order 2: Customer Name: John Smith, Email: john@example.com, Phone: 555-1234
• Order 3: Customer Name: John Smith, Email: john@example.com, Phone: 555-1234

Problems: Customer info duplicated, if John changes email must update all orders, potential for inconsistency.

Good Approach (Connection):

Customers table:

• Customer 1: Name: John Smith, Email: john@example.com, Phone: 555-1234

Orders table with connection:

• Order 1: Connected to Customer 1
• Order 2: Connected to Customer 1
• Order 3: Connected to Customer 1

Benefits: Customer info stored once, email change updates automatically everywhere, always consistent.

The most common relationship type. One record in Table A relates to many records in Table B.

Characteristics:

• One parent record
• Many child records
• Each child belongs to exactly one parent
• Parent can have zero, one, or many children

Examples:

• Customers → Orders - One customer has many orders
• Projects → Tasks - One project has many tasks
• Departments → Employees - One department has many employees
• Categories → Products - One category has many products
• Authors → Books - One author writes many books (simplified)

How It Works:

• Connection field added to the "many" side (Orders, Tasks, Employees)
• Users select which parent record to connect to
• Parent record automatically shows all connected children
• Can view children from parent, parent from children

A more complex relationship where records in Table A relate to many records in Table B, and vice versa.

Characteristics:

• Multiple records on both sides
• No single parent or child
• Requires junction/linking table
• Flexible, bidirectional relationship

Examples:

• Students ↔ Courses - Students enroll in many courses, courses have many students
• Products ↔ Orders - Products appear in many orders, orders contain many products
• Actors ↔ Movies - Actors star in many movies, movies have many actors
• Tags ↔ Articles - Articles have many tags, tags apply to many articles
• Skills ↔ Employees - Employees have many skills, skills possessed by many employees

How It Works:

• Requires three tables: Two main tables + one junction table
• Junction table has connections to both main tables
• Can store additional data about the relationship in junction table
• Example: Students, Courses, and Enrollments (junction)

The least common relationship type. One record in Table A relates to exactly one record in Table B.

Characteristics:

• Single record on each side
• Unique connection
• Often can be combined into one table
• Used for performance or organization

Examples:

• Users → User Profiles - Each user has one profile
• Employees → Parking Spots - Each employee assigned one spot
• Countries → Capitals - Each country has one capital

When to Use:

• Separate sensitive/private data
• Organize large numbers of fields
• Performance optimization (load data separately)
• Different access permissions

Let's create a Customers → Orders relationship:

Step 1: Create Tables

1. Create "Customers" table with fields:
   • Customer Name (Text)
   • Email (Email)
   • Phone (Phone)
2. Create "Orders" table with fields:
   • Order Number (Auto-Increment)
   • Order Date (Date)
   • Total Amount (Currency)

Step 2: Add Connection Field

1. Open "Orders" table
2. Click "+ Add Field"
3. Select "Connection" field type
4. Name: customer
5. Display Name: Customer
6. Connect to: Customers table
7. Save

Step 3: Configure Connection

• Display Field - Which field to show (Customer Name)
• Allow Multiple - No (each order has one customer)
• Required - Yes (every order needs a customer)
• Searchable - Yes (search for customers)

Step 4: Test Connection

1. Create a few customer records
2. Create order records
3. When creating order, select customer from dropdown
4. View customer record to see all their orders

Let's create Students ↔ Courses relationship:

Step 1: Create Main Tables

1. Create "Students" table:
   • Student Name (Text)
   • Email (Email)
   • Student ID (Auto-Increment)
2. Create "Courses" table:
   • Course Name (Text)
   • Course Code (Text)
   • Credits (Number)

Step 2: Create Junction Table

1. Create "Enrollments" table with fields:
   • Enrollment Date (Date)
   • Grade (Text) - optional field specific to relationship
   • Status (Select) - Active, Completed, Withdrawn

Step 3: Add Connection Fields to Junction

1. In "Enrollments" table, add connection field:
   • Name: student
   • Connect to: Students
   • Allow Multiple: No
   • Required: Yes
2. Add second connection field:
   • Name: course
   • Connect to: Courses
   • Allow Multiple: No
   • Required: Yes

Step 4: Test Many-to-Many

1. Create student records
2. Create course records
3. Create enrollment records, selecting both student and course
4. View student to see all their courses (through enrollments)
5. View course to see all enrolled students

Connection fields have these settings:

• Connected Table - Which table to connect to
• Display Field - Which field to show in dropdown (name, title, etc.)
• Allow Multiple - Select one or multiple records
• Required - Must select at least one connection
• Default Value - Pre-selected connection
• Searchable - Enable search in connection dropdown
• Create New Option - Allow creating new records from connection field

Control how connections display:

• Display Field - Primary field shown (Customer Name, Product Name)
• Additional Display Fields - Show extra fields (Email, SKU)
• Display Format - How multiple fields display together
• Sort Order - How options are sorted (alphabetical, date, custom)
• Filter Connected Records - Only show specific records in dropdown

Advanced connection options:

• Cascade Delete - Delete connected records when parent deleted
• Prevent Delete - Can't delete parent if children exist
• Limit Connections - Maximum number of connections allowed
• Connection Rules - Conditional connection options
• Bidirectional Display - Show connection from both sides

Connected Field Values let you display fields from connected records without creating separate fields. For example, once you connect an Order to a Customer, you can display the customer's email, phone, and address in the Order table or form without duplicating that data.

To access fields from connected records:

1. In your table or component
2. Add a column or field
3. Select "Connected Field"
4. Choose the connection (e.g., Customer)
5. Select which field to display (e.g., Email)
6. Field appears as if it's part of the table

In Orders table with Customer connection:

• Customer Name - From connection display field
• Customer Email - Connected field value
• Customer Phone - Connected field value
• Customer Address - Connected field value
• Customer Status - Connected field value

All these fields are pulled from the Customers table through the connection, without duplication.

Using connected field values:

• No Duplication - Data stored once
• Always Current - Changes reflect immediately
• Rich Display - Show all related information
• Flexible - Add/remove displayed fields easily
• Efficient - No additional database fields needed

Parent-child relationships are a specific type of one-to-many connection where the relationship is hierarchical:

• Parent - The "one" side, the container or owner
• Child - The "many" side, the items belonging to parent
• Hierarchy - Clear ownership and dependency

Typical parent-child relationships:

• Companies → Employees - Company owns employee records
• Invoices → Invoice Items - Invoice contains line items
• Projects → Tasks - Project contains tasks
• Albums → Songs - Album contains songs
• Categories → Subcategories - Hierarchy of categories

Parent-child relationships often use cascade operations:

• Cascade Delete - Delete parent deletes all children automatically
• Cascade Update - Changes to parent affect children
• Prevent Delete - Can't delete parent while children exist
• Orphan Prevention - Ensure children always have parent

Example: Delete Invoice → automatically delete all Invoice Items

From parent record, you can:

• View All Children - See complete list
• Add Children - Create new child records
• Edit Children - Modify child records
• Delete Children - Remove child records
• Summary Information - Count, totals, aggregations

Lookup fields pull a single value from a connected record:

• Purpose - Display specific field from connection
• Use Case - Show customer email in orders table
• Update Behavior - Updates when connected record changes
• Display Only - Can't edit through lookup

Summary fields aggregate data from connected child records:

• Count - Number of connected records
• Sum - Total of a numeric field
• Average - Average of a numeric field
• Min/Max - Minimum or maximum value
• List - Comma-separated list of values

Examples:

• Customer record shows: Total Orders (count), Total Revenue (sum), Average Order Value (average)
• Project record shows: Task Count (count), Completed Tasks (count with filter), Total Hours (sum)
• Product record shows: Times Ordered (count), Total Quantity Sold (sum)

Follow these connection best practices:

Planning Relationships

• Identify Entities First - What are your main tables?
• Map Relationships - How do entities relate?
• Avoid Redundancy - Don't duplicate what can be connected
• Think Real World - Model actual relationships
• Start Simple - Basic connections first, complexity later

Naming Conventions

• Descriptive Names - "customer" not "conn1"
• Singular vs Plural - "customer" (one) vs "tags" (many)
• Clear Direction - Name shows what you're connecting to
• Consistency - Use same patterns throughout app

Performance Considerations

• Index Important Connections - Faster queries
• Limit Connected Field Display - Don't show too many
• Use Summary Fields - Instead of displaying all children
• Optimize Display Fields - Choose efficient fields to display

Data Integrity

• Required Connections - Ensure critical relationships exist
• Cascade Carefully - Understand cascade delete implications
• Prevent Orphans - Don't leave child records without parents
• Validate Connections - Ensure connections make sense

Standard patterns you'll use frequently:

CRM Pattern

• Companies → Contacts (one-to-many)
• Contacts → Deals (one-to-many)
• Deals → Activities (one-to-many)
• Users → Activities (one-to-many - assigned to)

E-Commerce Pattern

• Customers → Orders (one-to-many)
• Products ↔ Orders (many-to-many via Order Items)
• Categories → Products (one-to-many)
• Products → Reviews (one-to-many)

Project Management Pattern

• Projects → Tasks (one-to-many)
• Tasks → Subtasks (one-to-many)
• Users ↔ Projects (many-to-many via Team Members)
• Tasks → Comments (one-to-many)

Content Management Pattern

• Categories → Articles (one-to-many)
• Authors → Articles (one-to-many)
• Articles ↔ Tags (many-to-many)
• Articles → Comments (one-to-many)

Practice creating connections:

Exercise 1: Customer Orders System

1. Create "Customers" table with:
   • Customer Name, Email, Phone, Address
2. Create "Orders" table with:
   • Order Number (Auto-Increment), Order Date, Total, Status
   • Connection to Customers (required)
3. Add connected fields to Orders:
   • Customer Email, Customer Phone from connection
4. Add summary field to Customers:
   • Total Orders (count), Total Revenue (sum of Order Total)
5. Create 5 customers and 15 orders
6. Test viewing orders from customer, customer from order

Exercise 2: Project Tasks System

1. Create "Projects" table:
   • Project Name, Start Date, End Date, Budget
2. Create "Tasks" table:
   • Task Name, Due Date, Status, Priority
   • Connection to Projects (required)
3. Add summary fields to Projects:
   • Task Count, Completed Tasks, Pending Tasks
4. Create 3 projects with 10+ tasks each
5. Test task organization and summaries

Exercise 3: Many-to-Many System

1. Create "Students" table:
   • Student Name, Email, Student ID
2. Create "Courses" table:
   • Course Name, Course Code, Credits
3. Create "Enrollments" junction table:
   • Connection to Students (required)
   • Connection to Courses (required)
   • Enrollment Date, Grade, Status
4. Add summary fields:
   • Students: Course Count
   • Courses: Student Count
5. Create 10 students, 5 courses, 30 enrollments
6. Test many-to-many relationships

Common connection issues and solutions:

Connection Not Showing Options

• Problem: Connection dropdown empty
• Solution: Ensure connected table has records, check display field is set

Can't Delete Parent Record

• Problem: "Cannot delete, child records exist"
• Solution: Delete or reassign children first, or enable cascade delete

Connected Field Not Updating

• Problem: Changes to connected record don't reflect
• Solution: Refresh page, check field mapping, verify connection exists

Too Many Connection Options

• Problem: Dropdown has thousands of options
• Solution: Enable search, add filters to connection, consider different UX

Summary Field Wrong Value

• Problem: Count or sum showing incorrect number
• Solution: Check summary field configuration, verify filters, recalculate

Advanced concepts to explore later:

• Self-Referencing Connections - Table connects to itself (org charts, nested categories)
• Conditional Connections - Show different connection options based on other field values
• Multi-Level Connections - Connect through connections (A→B→C)
• Connection Automation - Auto-connect records based on rules
• Connection Security - Limit connections based on user permissions

In this comprehensive lesson, you've learned:

• Connection Basics - What connections are and why they're essential
• Relationship Types - One-to-many, many-to-many, one-to-one
• Creating Connections - How to add and configure connection fields
• Connected Field Values - Displaying data from connected records
• Parent-Child Structures - Hierarchical relationships and cascade operations
• Summary Fields - Aggregating data from child records
• Best Practices - Planning, naming, and optimizing connections

Before continuing, ensure you can:

• Explain what connection fields are and why they're important
• Identify one-to-many vs many-to-many relationships
• Create a connection between two tables
• Configure connection field settings
• Display connected field values in tables and forms
• Create a junction table for many-to-many relationships
• Add summary fields to aggregate child data
• Understand cascade delete and its implications
• Apply connection best practices

You've mastered the most powerful field type. Next, you'll learn about data components - how to display and interact with your connected data.

Knowledge Check (To Be Added)

Quiz will cover:

• Identifying relationship types
• When to use one-to-many vs many-to-many
• Creating junction tables
• Connected field values
• Summary field calculations
• Cascade operations
• Connection best practices