Best way to structure data when a task can be linked to a child or a parent

[dcooldrummer]

Can't seem to find this on the community board, but if it has already been answered - I'd appreciate a link. 

I have Projects and Subprojects. The latter being the child. So there are a few Subprojects for every Project. On the other hand, I have Project managers. 

I want to have a "Tasks" table with all the tasks assigned to Project managers. 

My issue is that I want tasks to be EITHER created related to the Subproject, OR related to the main Project. 

The same exact relationship issue I encounter is when I have "Payments". 
A payment can be to a subcontractor - and therefor related to a Subproject. 
OR a payment can be to a Sales agent who brought the Project - and then it would be to the project. 

If I create two tables for these (i.e. "Project Tasks" and "Subproject Tasks" or "Project Payments" and "Subproject Payments") then it really just duplicates a lot and it also makes it impossible to create a normal interface to see all tasks assigned to me for example. 

Would appreciate any pointers on how to structure this correctly. 

Thank you!

[patrick-F]

Hi  @dcooldrummer ,

There are multiple ways how to solve this one. One way that I like is to have the parent and the child record on the same table. Then you can link the records within the same table.

The link will show up only on one of the linked records. So you can link all the child records to the parent record and easily filter by the "linking field". (Below you should see the screenshot.)

Since projects and subprojects then are both in the same table, it should be easy to link it to the tasks. 

Best,  Patrick @Monino Solutions

[dcooldrummer]

Hey @patrick-F 
Thanks!!
Since the Project - Subproject structure is set up, and the Subprojects have a completely different set of columns than the project, this solution is a little less what I was looking for. 
Do you have any other options to get this done? 

[TheTimeSavingCo · Accepted solution]

Would this work?

Link to base

[dcooldrummer]

This is perfect @TheTimeSavingCo 
Is there some way that the consolidated field will be the actual link to the project / subproject?

[TheTimeSavingCo]

Not that I know of I'm afraid