Table design question for tracking projects with and without sub-projects

[jcclow]

Beginner FileMaker user here. 

I am working through the Philosophy of FileMaker series on Udemy and have a question about a project I'm working on. I am building a FileMaker project to track projects at the shop I work at. Sometimes we have projects that we break into smaller projects and assign different engineers to the sub-projects, but the sub-projects are always part of a larger project that has a specific number per overarching project. For example, project 584 might be Charleston Courthouse, but it gets split into 1st floor, 2nd floor etc. Sometimes we don't break the projects up and the whole project goes to one engineer. I have a Project Manager table, an Engineer table, a Projects table, but I am wondering how to handle when the project gets split into sub-projects. 

I am leaning towards the sub-projects being a separate table because sometimes the project doesn't get split up and I think trying to track projects that get split up in the same table as projects that don't get split up will be confusing. So all projects will be represented in the Projects table, but projects that get split up will have a relationship with a separate table called Sub Projects where each sub project gets its own line. I'd love to hear what some more seasoned FileMaker developers would do.

[comment]

It depends on whether - and to what extent - a subproject is also a project.

For example, you say that there is an engineer assigned to each project. If there is a portal of assigned projects on a layout of the Engineers table, should it show only the "main" projects? And any subprojects would be shown in a separate portal?

How about a report that shows the status of each open project? Should it also include subprojects? 

Another consideration is what fields are required to track a project, and how different is this from the fields required to track a subproject.

If (as I would suspect) the two tables would have mostly the same fields, and there are cases where subprojects need to be listed alongside "main" projects, then I would put them in the same table with a field for ParentProjectID (which would remain empty in a record of a "main" project).

 

[jcclow]

Those are all really good questions to ask before I get too far along...thank you.

Lets's start at the top...

On 8/7/2024 at 11:53 PM, comment said:

If there is a portal of assigned projects on a layout of the Engineers table, should it show only the "main" projects? And any subprojects would be shown in a separate portal?

Ideally, I want to be able to sort/view the data a couple of different ways

1. By Engineer - Only the projects assigned to the engineer would show
2. By Project - If project 536 consisted of sub projects 1st floor, 2nd floor, etc. I would want to see all projects associated with project #536

On 8/7/2024 at 11:53 PM, comment said:

How about a report that shows the status of each open project? Should it also include subprojects?

For our Monday morning team meeting, we would want to be able to see all open projects in the following format...

[Project #] - [Project Name] - [Sub-project (if one exists)] - [PM] - [Engineer]- [Install Date] - [Status] etc.

 

On 8/7/2024 at 11:53 PM, comment said:

Another consideration is what fields are required to track a project, and how different is this from the fields required to track a subproject.

They are identical.

 

On 8/7/2024 at 11:53 PM, comment said:

If (as I would suspect) the two tables would have mostly the same fields, and there are cases where subprojects need to be listed alongside "main" projects, then I would put them in the same table with a field for ParentProjectID (which would remain empty in a record of a "main" project).

Does this then become a boolean where if there is a sub-project, a new record will be added with the Project Number, Project Name, Sub-project Name, PM, Engineer, etc.?

[comment]

2 hours ago, jcclow said:

Does this then become a boolean where if there is a sub-project, a new record will be added with the Project Number, Project Name, Sub-project Name, PM, Engineer, etc.?

I am afraid I don't understand what you mean by "boolean" in this context. As I said, in the absence of contradicting indications, I believe you should put all your projects in the same table and use a self-join to link a project to its subprojects.

You can create subprojects directly from a "main" project's record by entering them into a portal. That is not the only way possible, and there are some additional considerations you may have to implement into the UI (for example, prevent a subproject from having subprojects of its own?) but these should have no bearing on your data structure.