Manage Many-To-Many relation between Tasks & Projects SharePoint lists; by storing the ProjectsIDs inside the Tasks field OR by creating a seperate list

[johnjohn123]

I have 2 SharePoint online lists:-

1. Task
2. Project

where a task can be associated with zero,one or multiple projects.

Now I have 2 approaches to store this Many To Many relation between the Task and the Project list inside SharePoint:-

1) To store the ProjectIDs inside a SharePoint lookup field OR inside a single line of text and separate the ids by ";"..

2) Second approach; to build a third SharePoint list which store the TaskID and the ProjectID something as follow:-

ID | ProjectID | Task ID

1  | 100          |  1

2  | 200          |  1

3  | 100          |  2

4  | 300          |  3

Now in SharePoint it allows us to store such an info inside a field, without having to create a second list, but the issue inside Power Apps is with the delegation errors we will get if we try to get Tasks under a Project, either if we try to filter the lookup field, or do a search on a single line of text field which contain the ProjectIds separated by ";".. so is creating a second list a valid approach to follow? and if it is so, then why i did not find any online documentation which uses this approach ?

Thanks in advance for any help

[Drrickryp]

@johnjohn123

Use a third list. This is standard for classical database design. It is called a junction table and the principles are well established. The shortcuts will cause problems. 

[johnjohn123]

@Drrickryp  But the confusion comes as SharePoint allow us to store multiple values inside a single field, such as using choice,lookup, people/group which allow multiple values/selections.. so seems we should stay away of these multi-value fields types and create a separate list represent the M-to-M relation??.. but did you ever find any link/documentation which mention this approach? i simply did not find any documentation describing/suggesting this approach to overcome the delegation warning we get when we try to filter choice,lookup,people/group fields which allow multiple selections.. did you get my point, why i asked this question?

Thanks

[Drrickryp]

@johnjohn123 

SharePoint existed for years before PowerApps and many of those properties were created to make it easier to deal with lists within SharePoint as it is not a relational database.  PowerApps was layered over SharePoint and the same functions can be re-created within PowerApps following established database principles.  The native back end database for PowerApps is Dataverse (formerly CDS), and the relationships are part of it.  To use SharePoint, I suggest reading @WarrenBelz 's blog Practical PowerApps https://www.practicalpowerapps.com/  and my blog post here.   https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-... . 

As my dad told me, just because you CAN do something doesn't mean you SHOULD do it.  

[johnjohn123]

@Drrickryp Ok thanks for the reply.. now to keep the question precised , so you are saying that creating a M-To-M sharepoint list is the way to go, rather than using Multi-Values fields?

[Drrickryp · Accepted solution]

@johnjohn123 

Yes