Relation between databases

[SoloInter]

Good morning,

Objective: Display the list of matches. Access the details of a match.

We already have a database containing the players (Example of fields: last name, first name, date of birth, number, position on the field, etc.)
We are thinking of creating a database containing information on football matches (Example of fields: date, team_a, score_a, team_b, score_b, competition, etc...)

Each match must be able to include players. 

What do you think is the best procedure to follow to configure the databases together in order to make it possible to enter the details of a match and add several entries present in another table (here players)?

At the end we would therefore have a page detailing the scores and the players who took part in the match.

Thanks for your advices. 🙏

Edited May 28 by SoloInter

[BN_IT_Support]

Hi,

You said 'database' - did you mean a 'Pages Database'? If so, that is really only a single table.

Your question really relates to standard SQL relationships - are they one-to-many, many-to-one or many-to-many.

If you have one-to-many/many-to-one relationships then it is fairly easy - for example, teams and players. You have a team with a team_id. You have many player records and every player record has a reference to the team_id. In that way you can list all the teams and for each team you can find the players that have a matching team_id so you can find the members of the team (or, at least, the squad).

Your scenario seems to invite many-to-many relationships so you will need 'intermediate' records that 'cross-reference' between other records. Personally, I think that gets overly complex if you try to do that within Pages Database and I would write my own Application (within Invision) and define multiple tables. (We did this for our 'volunteer recruitment' application/database - we had many examples of many-to-many, for example users could create many applications (for different roles) and we could have multiple applications for a single role.)

In your case you have:

• team: team_id, chairman, coach, (address if you only cater for home and away, but if you include neutral ground - e.g. Wembly for the Cup Final then you would need 'venue' records and include the 'venue_id' instead of the address).
   
• players: player_id, name, DoB, default_position (might not always play every match in the same position), team_id (assuming they can only belong to one team then it is a many-to-one relationship, but if you want to track players over time as they switch teams then you need an intermediate many-to-many relationship)
   
• venues (not needed if you only cater for home and away and include the address in the home team record): venue_id, address
   
• match: match_id, date, venue_id
   
• team-to-match (many-to-many cross reference between teams and matches): xref_id, match_id, team_id, score (by this team as the other team has its own score). You typically have two of these team-to_match records linked to every match record...
   
• player-to-match (many-to-many cross reference between players and matches): xref_id, player_id (this player was actually picked for this match), position (this player played this position in this match), score (this player scored this much in this match)

Then you need some fancy SQL 'joins' to pull the information that you want...

John

[SoloInter]

Thanks @BN_IT_Support for your answer. 

Yes I was speaking about database from app Pages.

I was asking me if it was possible to do something like that because there is a way to joins table using Pages.

On a database, when we add fields, there is an option "Database relationship".

 

I was asking me if we can use that option and get 2 tables (Matchs & Players) linked together. 

At the end we want add records on "Matchs" and be able to select severals players on "Players", and show the record with full information (matchs details AND players selected).

Or maybe it's not really possible and not "a good way" to do what we want.

 

[BN_IT_Support]

Hi - you need to get the type of relationship straight and clear.

If you only want a 1-to-many (or many-to-1) relationship then you can probably do it with Pages Databases.

If you actually want/need a many-to-many relationship (which is what I think you will need) then it will almost certainly be easier to write an application to do it. I won't say that it would be "impossible" to do many-to-many with Pages Databases but I believe that it would be really hard work. You are talking about just two tables/databases but to do many-to-many you need an additional table/database that sits between the other two and links the records together.

You stated the problem as having:

• Many matches
• Many players
• Requiring a relationship so that you could add many players to a match

As stated it appears to be a 1-to-many (that is each match can have many players) and that could be done with Pages Databases relatively easily.

But, appearances can be deceptive. The above (1-to-many) will only work if each player can only play in 1 match (as that's where the "1" comes form in 1-to-many). It is far more likely that your players can play in many matches -- i.e. they play in one match this week-end and a different match next week-end and so on. So, you have both "matches can have many players" and "players can play in many matches" and that is a many-to-many relationship that becomes extremely complex to implement with Pages Databases.

John

[SoloInter]

@BN_IT_Support Well thank you very much for this clear and precise answer. You answered my doubts perfectly. 🙏