Another One to Many to Many conundrum-

[labyrinthian]

If anyone can help shed light on these relationships I would be forever indebted to you. With the help of an FMP expert, I’ve been trying to sort out the best way to set up the relationships between 3-4 tables. But we’ve been going around in circles for a while, and it would be great to get fresh perspectives on how best to do this.

We are focusing on three tables: Content - Projects - Subprojects. Each piece of content can be associated with multiple Projects, some of which have multiple associated Sub-Projects. We currently have a join table connecting Content- Projects, and a direct relationship between Projects-Subprojects. So the set up is: Content - [Join-Content-Projects] - Projects – Subprojects. Additionally, we have second occurrences of Projects and Subprojects and a Join Table btw Content and Sub-projects, and the relationships are: Content – [Join-content-Subprojects] - Subprojects 2 – Projects 2.

This seems to be semi-functional, but it isn’t quite as streamlined as we would like it. We want to be able to A:) Find and Sort Content based on which Project or Subprojects it is associated with. Content will have a Project, but not always a Subproject. B:) We want to be able to set up fields such that when you search for a Project, you can then select Subproject from the related list of that Project’s Subprojects (not the overall list of Subprojects). C:) We want to be able to generate list of Content, and eventually contacts, based on the connection to Projects and Subprojects.

In the Content Layout we have portals to the Projects and Subprojects field, but the fact that we can’t put a portal to Subprojects inside of the portal to Subprojects seems to be making it difficult to tag our data properly.

We will replicate this relationship with our Contacts table as well, so we want to make sure everything is as solid as possible. It doesn’t seem like an unusual set of relationships, but for some reason I’m having a terrible time wrapping my mind around how to make it work. I would appreciate any and all advice anyone can provide!

[comment]

Content will have a Project, but not always a Subproject.

What exactly is a subproject? If content can be associated with a subproject in the same way it is associated with a project, then most likely projects and subprojects should be in the same table. Of course, this is no more than a guess; some background regarding the real things this is supposed to track would be helpful.

[labyrinthian]

What exactly is a subproject? If content can be associated with a subproject in the same way it is associated with a project, then most likely projects and subprojects should be in the same table. Of course, this is no more than a guess; some background regarding the real things this is supposed to track would be helpful.

A sub-project would be individual events held under the umbrella of a greater project. For example, since we are a non-profit we collaborate with different funders on different projects. The projects then have sub-projects, i.e. Conference 2011, Conference 2012, Fundraiser XYZ. So it would be nice to have tiers of specificity - the Overall Projects, and then the Subprojects within that.

Initially I had the Projects and Subprojects as check-box Value lists in the Content Table, but when it became apparent we would need to be able to create lists of content and contacts by Project and Subproject, it seemed prudent to have them as separate table relationships. Then I considered having a Projects table with a Sub-project field, but thought that that would limit search- and reportability by Subproject. Would love any advice you have.

[comment]

I still don't see the full picture. Perhaps you should rename Subprojects to Events and make it a business rule that content can only be assigned to an event. IOW, a project must have at least one event in order to get off the ground. Again, this is mostly theoretical, because I don't know what "content" is.

Another possibility is to add a SubprojectID field to the ProjectContent join table. That way, content is always assigned to a project and - optionally - to a specific subproject within the project.

[labyrinthian]

I still don't see the full picture. Perhaps you should rename Subprojects to Events and make it a business rule that content can only be assigned to an event. IOW, a project must have at least one event in order to get off the ground. Again, this is mostly theoretical, because I don't know what "content" is.

Another possibility is to add a SubprojectID field to the ProjectContent join table. That way, content is always assigned to a project and - optionally - to a specific subproject within the project.

Sorry for my ambiguity - Content includes internal documents, references for books, articles, websites, images, etc. that we use internally in our organization. Projects includes the Projects we work on with various collaborators. We call Subprojects 'Subprojects' and not Events because they are not always events, but sometimes operating programs that happen under the umbrella of a larger project. So a project does NOT have to have a Subproject. Some Content will ONLY have a project, if its relevant to the general operation of a Project, and not specific to a Subproject.

I like this suggestion of adding the SubprojectID field to the join table. So you mean to connect both the Projects and Subprojects Tables through the ProjectContent join, instead of our current set-up with parallel occurrences and 2 separate join tables? This outcome sounds like exactly what we would like to accomplish - to have the Content always assigned a project and optionally to have a subproject - but we are having a hard time getting this to work with the 2 Join tables between Content and Proj and Subproj. I will explore this. The other question we've been struggling with is how to limit a Sub-project's selection based on the Project that has been selected first? In our current set-up we are unable to get this working, but maybe with the single Join table it will be more straight forward.

Thank you so much for your help, comment.

[bruceR]

You might also want to look at the book, "The Data Model Resource Book, Volume 3" by Silverston and Agnew. It shows many variations for this kind of modeling.

[comment]

We call Subprojects 'Subprojects' and not Events because they are not always events, but sometimes operating programs that happen under the umbrella of a larger project.

Well, the thing that bothers me here is that - apparently - a project can serve as an umbrella of itself. Usually, resources are not assigned to an umbrella. I'd suggest you go over the list of fields in Projects and in Subprojects and see if there is any duplication.

we are having a hard time getting this to work with the 2 Join tables

There would be only one. Roughly, your RG would look like:

SubProjects > - Projects -< ProjectContent >- Content

	                            v

                                    |



			   SubProjects 2

how to limit a Sub-project's selection based on the Project that has been selected first?

Define a value list using values from Subprojects::SubprojectID, show only related starting from Projects.

[labyrinthian]

comment, thank you so much for this feedback. This structure ended up working out for us. It really helped to have fresh eyes on this. We are testing use of that structure and using the Project and Subproject tables as sources for value list fields in the Content Table. The portals we were trying to use to tag by Project and Subproject ended up becoming too cumbersome to be used for easy data entry so we have switched back to value lists. But this is still very much under construction. Thanks for your help; consider us indebted to you!