On which side (One or Many) we need to add the Lookup field

Dear john,

From my research, both of your scenarios come down to standard relational modelling, so let me address each in turn.

Scenario 1: Business Case to Financial Info (one-to-many)

The lookup should go on the Financial Info list, as a single-value lookup to the Business Case list. The reasoning is that in a one-to-many relationship, the reference always lives on the many side. Each Financial Info item needs to know which Business Case it belongs to, which is exactly the role a foreign key plays in a relational database.

If you instead placed a multi-value lookup on the Business Case list, you would be manually maintaining a list of related Financial Info items on the parent record. That becomes fragile as records are added or removed, and it runs contrary to how one-to-many relationships are normally modelled. With the lookup on the child side, the relationship maintains itself naturally as items are created.

Scenario 2: Business Case to Department (many-to-many)

Since one Business Case can involve multiple Departments, and one Department can participate in multiple Business Cases, this is a genuine many-to-many relationship. There are two reasonable approaches.

Option 1: Multi-value lookup

Add a multi-value Department lookup on the Business Case list. This is often perfectly adequate for smaller or simpler solutions.

Two caveats worth being aware of, though:

• Multi-value lookup columns cannot be indexed, which becomes a problem as the list grows.
• They have limited filtering support in views, and can behave awkwardly in Power Automate and Power BI.

Option 2: Junction list

Create a third list, for example Business Case Departments, containing:

• A single-value lookup to the Business Case list
• A single-value lookup to the Department list

Each row then represents one relationship between a specific business case and a specific department. A business case involving three departments would have three rows in this list.

This design is more scalable because it:

• Avoids large multi-value lookup fields entirely.
• Lets you store additional attributes on the relationship itself, such as the department's role, budget allocation, or approval status.
• Keeps both lookups indexable and filterable.
• Reports far more cleanly in Power Apps, Power BI, and Power Automate.

It is also worth noting that SharePoint enforces a lookup column threshold of 12 per view, so heavy use of lookups across a solution can hit that limit. A junction list helps keep this manageable.

In short

• One-to-many: lookup on the many side, single value.
• Many-to-many: multi-value lookup for simple cases, junction list for anything you expect to grow or report on.

I hope this information helps clarify the situation and provides you with workable solutions. Should you have any further questions or need additional assistance, please don't hesitate to reach out. I'm always happy to help. Have a wonderful day! 

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.