Connection Types

What You'll Learn

This guide explains the different connection types available in Knack, with a focus on One-to-Many and Many-to-Many relationships. You'll discover when to use each type and how they affect your data structure.

Types of Connections

One-to-Many Connections

In a One-to-Many connection, one record in the parent table can be linked to multiple records in the child table, but each record in the child table connects to only one record in the parent table.

• One record connects to multiple records in another table
• Example: One Sales record can have one Product. There can be many Products in the Sales table.

In a One-to-Many connection between Products and Sales, each Sales record connects to exactly one Product, but a single Product can be referenced by many different Sales records.

It's like having a dropdown menu in the Sales table where you can select one Product from the complete list of available Products. For example, if you sell coffee mugs, the same mug product could appear in hundreds of different sales records, but each sale can only be for one specific product.

Common Use Cases

• Employees belonging to departments (each employee has one department, departments have many employees)
• Orders placed by customers (each order has one customer, customers have many orders)
• Products in categories (each product has one category, categories have many products)

Many-to-Many Connections

In a Many-to-Many connection, records in both tables can be linked to multiple records in the other table.

• Multiple records in one table connect to multiple records in another table
• Example: Many sales representatives can be assigned to a region. Each region has multiple reps, and each rep can be assigned to one or more regions.

Below we have a Sales Regions table and a Sales Team table:

In the Sales Region table, each region can have a whole team of sales reps assigned to it, and each rep can work in more than one region.

If you are looking at Southeast's region record, you might see it's connected to three different sales rep records, while some of those sales reps might also be assigned to the Northeast.

Common Use Cases

• Students enrolled in courses (students take multiple courses, courses have multiple students)
• Products in orders (orders contain multiple products, products appear in multiple orders)
• Employees assigned to projects (employees work on multiple projects, projects have multiple employees)

Choosing the Right Connection Type

Consider these factors when selecting a connection type:

1. Relationship Nature: How do the records naturally relate? Can one record on either side connect to multiple records on the other side?

2. Data Entry Flow: Where will users primarily enter data? This can inform where to place the connection field.

3. Reporting Needs: What reports or views will you need to create from the connected data?

4. User Interface: How will users interact with the connections? Which approach would make selection most intuitive?

Caution: For best results, we recommend using either One-to-Many or Many-to-Many connections, even though other options are available. These two connection types cover most use cases and provide the most robust and efficient ways to structure your data.