How to model a many-to-many relationship between tables
A many-to-many relationship between tables applies when records on both sides can connect to several on the other: one payable can belong to marketing, to finance, or to both, and each cost center holds many payables. In Jestor, you create it with the connected tables field type.
Why this is different from one-to-many
A one-to-many connection assumes one side is exclusive. A payable has one supplier, and that is true.
Cost centers break that assumption. A single invoice for a shared service legitimately belongs to more than one area, and forcing it into one distorts every report built on top of it.
Where many-to-many is the honest model:
- Expenses split across several cost centers
- Products belonging to multiple categories
- Employees assigned to more than one project
- Tickets tagged with several affected systems
- Contracts covering multiple business units
Choosing the right connection type
| Field type | Relationship | Example |
|---|---|---|
| Connected records | Many to one | Each payable has one supplier |
| Connected tables | Many to many | A payable spans several cost centers |
| Bidirectional connection | Visible from both sides | Navigation in either direction |
Step by step to create it
- Open the table that will hold the connection, such as payables.
- Add a column and search for connected options.
- Select connected tables, which is the many-to-many type.
- Name the column after the entity, such as cost center.
- Pick the target table, in this case the cost centers registry.
- Create the field and test it by assigning two values to one record.
Why this unlocks cost allocation
Once a payable can reference several cost centers, allocation becomes possible: an invoice of a given amount can be distributed across the areas that consumed the service, rather than being dumped entirely onto whichever one was listed first.
- Both sides can hold many: the model matches the reality
- Navigation in both directions: from the payable and from the cost center
- Foundation for allocation: splitting an amount across areas
- No duplicated records: one payable, several connections
Frequently Asked Questions
When should I use many-to-many instead of many-to-one?
When records on both sides can legitimately connect to several on the other. See jestor.com.
Can I see the connection from the other table too?
Yes. The relationship is visible and navigable from both sides.
Does this duplicate the record?
No. One record holds several connections rather than being copied.
Video Tutorial: Step by Step
With Jestor, you can automate workflows, connect teams, and build internal systems your way, all without code and powered by AI. Discover Jestor at jestor.com and see how to take your company's operations to a new level of efficiency and control.