using a filter versus a join to narrow down records in a portal

[jjjjp]

I am in the early stages of generalizing a database I built several months ago for setting up and managing workshops in a university setting, and I would like to get advice about a key design decision that I will be stuck with from here on.

The workshops will belong to what I refer to as an organization, and each organization will be able to mount several programs. (Prior to now, you could think of my database as consisting of a single program, within a single organization.) What I plan to do is add an organization id field and a program id field to the records of most of my key types of data: presenters, locations, times, dates, etc. I want to offer flexibility for each organization to choose whether it wants to share a particular type of data across programs or whether it wants to keep the programs separate as far as that type of data is concerned. If the data is shared across the whole organization, then the organization id field contains the organization id and the program id is empty; if the data is shared only between programs, then the program id field contains the program id, and the organization id is empty. In any Filemaker session, the organization and program stays fixed. When a database administrator logs in, he or she is shuttled to a specific program, and a global field is set for the both the program id and the organization id. For each type of data a global field is also set indicating whether the data is shared across the organization or only within programs.

I have a fair number of portals for each of the data in question, and the relationship between table and portal records has till now been a simple cartesian join. I do some portal filtering (for purposes not relevant to this discussion), but since there has till now been only one program that all of the data belongs to, there is not a great deal of filtering from the full set of records. But now the amount of filtering that needs to occur is going to be considerably greater, and I am unclear whether it is better (1) to create a join in the relationship table between the organization id field in the portal records and the global field for the current organization id, as well as a similar join for the program id, or (2) to keep the cartesian self-join and use filtering to create the same result. If I use solution (2), I will have to double the number of layouts and complicate the relationship table somewhat. So solution (1), using filtering, seems on the whole more elegant and therefore preferable (albeit the logical expression for filtering will be a bit complex, given that filtering was already going on), but I am concerned that (1) may not be the best solution from the perspective of runtime performance. I just don't have the knowledge to say myself whether filtering in this case will become an issue as the size of the database starts to grow. My question boils down to the following general question: Are joins intrinsically more efficient than filters?

What do experienced Filemaker users tend to do in similar situations? I imagine this kind of choice arises a lot in one form or another, though I didn't know where to begin to find the issue in the Forum.

[jjjjp]

I've thought about this some more over the last day and now see how I can avoid both relying on filters and duplicating layouts. I need to set two new global fields to use as parents in the match relationship. The first would be set to the organization id if the data is shared between organizations, and empty otherwise. Similarly for the second global field, but wrt programs. Should have seen this earlier.

If anyone has anything to say about filters versus joins as a way of restricting the rows in a portal, I am still interested. Clearly filters give one more flexibility in just how one can do the restricting, but is there a significant cost in speed as the number of records being filtered grows?

[comment]

Filtering portals *is* inherently slower, because it's not drawing on field indexing (that's how you can filter portals based on unstored calculations in the child table).

[Vaughan]

Correct me if I am wrong, but portal filtering is only for display: ie, it's an interface device and does not work on the data level.

[comment]

That too.