Best Practices for Connections

What You'll Learn

This guide provides practical recommendations for creating and managing connections between tables. You'll discover strategies for efficient database design, optimal connection placement, and maintaining clean data relationships.

Database Design

Before implementing connections, consider these database design principles:

• Plan your connections before building: Sketch out your data structure and relationships before creating tables and connections
• Use meaningful connection names: Choose clear, descriptive names that indicate the relationship
• Review relationships between tables: Use the Data Model to visualize and verify your table connections
• Add connections to child tables in one-to-many relationships: This makes the interface more intuitive and manageable

Connection Placement

Where you place your connection fields can significantly impact usability and performance:

For Many-to-One Relationships

When adding the connection to the Application table, the connection field appears as a lookup field:

• You can easily select which Job the application is connected to
• The connection field shows up as a dropdown with available Job options
• This makes it simple to edit or view which Job an Application is connected to

If you added the connection to the Job table instead:

• You would need to connect to many or all Application records
• This makes the interface more complicated and less intuitive
• It's harder to manage the connections efficiently

For Many-to-Many Relationships

• Place the connection in the table that will be used or edited more frequently
• Consider your typical workflow and access patterns
• Think about which side of the relationship will have more records

Practical Examples

Contact Directory Structure

• Companies & Contacts: Add the connection field while on the Contacts table, allowing each contact to be linked to one company
• Notes Management: Add the connection field while on the Notes table, enabling multiple notes to be associated with each contact
• Location Tracking: Create connections between Companies and Locations while on the Locations table to track multiple business locations

Project Management Structure

• Tasks & Projects: Add the connection field while on the Tasks table to associate multiple tasks with each project
• Team Assignments: Use many-to-many connections between Teams and Projects tables
• Resource Allocation: Connect team members to multiple projects while maintaining clear project ownership

Order System Structure

• Orders & Products: Add connection fields while on the Orders table for linking multiple products
• Customer Management: Connect orders to customers for order history tracking
• Inventory Control: Link products to inventory locations for stock management

Best Practices Checklist

• Place many-to-one connections on the "many" table
• Consider user workflows when placing many-to-many connections
• Test connections thoroughly before deleting or modifying them
• Document your connection structure for future reference
• Create a few records first, so when adding connected records, you'll have existing records to connect to
• Use meaningful display fields to make record selection intuitive
• Plan for scalability when designing connections
• Consider how data will be queried and reported on when placing connections

Tip: While most relationships require only one connection field, some specific cases may require two connection fields between the same tables.