I am designing a soccer club db. I am stuck on a little problem with relationships. I have two tables: Coaches and Teams, the coaches can perform several roles for different teams, and one team can have several coaches/ managers, etc. So it is a M to M relationship.

I have a joint table called Roles, so new roles are created in a portal in the teams table and also displayed in the coaches table showing all the roles for each coach. So far so good. The problem arrives when I create another joint table: Preferences that creates all the preference roles that each coach wants to have for the upcoming season. To complete the relationship, I have to create another table occurrence for teams: TeamsPreferences

The problem is that I cannot display correctly the Preferences portal in the Teams table, I am aware that this is because it is in a different context, but how can I display both portals on the same layout? They need to be side by side. What is the best way to solve this?

I have included a sample file.

Soccer.fp7.zip

Edited March 3, 200619 yr by Guest

The way you're going at it, you would need an arrangement like this:

Coaches <-> Roles <-> Teams 2

Coaches <-> Preferences <-> Teams 3

Teams <-> Roles 2 <-> Coaches 2

Teams <-> Preferences 2 <-> Coaches 3

However, a role is but a preference come true, so it could be simpler to put them both in the same table and have a field indicate the type.

Hi comment, thanks for your answer, I joined Roles and Preferences into one table, whick makes sense, and set up the relationship configuration like you suggested, but I still don't understand how I can display both portals from different relationships on one layout.

Oh no: it's either one or the other. If you have combined them, and you want to see both types, side by side, you'll need a TO for each portal and a field to filter the portal by type.

For example, let's say an actual Role is marked as 1, while a Preference is marked as 0. To see, from Coaches, a portal of actual roles, you need a field to hold the number 1 (either a global or a calculation = 1).

Then you can add to your graph:

Coaches <-> Roles 2:

Coaches::CoachID = Roles 2::CoachID

AND

Coaches::calc1 = Roles 2::Type

And so on... It's quite a lot of TO's. Perhaps now that you have joined them, a single portal with a global field (show Roles/Preferences/All) should be enough?

Hi comment, once again thanks for your answer. I could try the portal filtering, but might be a little bit tricky since one of the portals is for data entry and the other is just to see data.

From the user's point of view, the ideal situation is to see a portals with the candidate couches on one portal (in the teams table), and right next to it having another portal to insert those coaches who got the roles for each team.

I am not so convinced with the portal filtering solution, I might have to deal with a pop-up window that displays the second portal from another TO, or some other workaround, please don't stop throwing in ideas, and thanks again.

HI comment, I've got it! The two portals side by side on both tables. You gave me the clues, this is what I did:

I used only one joint table like you suggested, added an extra field and two extra keys.

Coaches <-> PreferencesRoles <-> Teams.

Displays the preference portal on both tables.

Coaches 2 <-> PrefereceRoles 2 <-> Teams.

(new set of keys)

Allows the creation of roles at the teams table.

Coaches <-> PrefereceRoles 3 <-> Teams 2.

displays the roles portal at the coaches table.