Database Design for Player Availability Tracking in Volleyball Club Management App

[Rob_Cap]

Hello,
I am building an app to manage a volleyball club.
The club has various team (for the sake of simplicity let’s say 2: TeamA and TeamB).

Each team has a training schedule and a game schedule, that are displayed on each Team page.

I have a Training_Schedule Table (columns are: date, time, location, team) and a a Game_Schedule Table (similar columns). I also have a Players Table.

My goal: Each player must be able to indicate (checkbox) if they are available for the training (or game), so the coach can plan and track presence/absence.

I am lost on how to achieve this.

Since each player has a Player page where only they (+admin) have edit rights, I thought they could see the training calendar on their page and tick the checkbox, but where should the data be recorded?
Shall I add then a column for each player on the Training_Schedule Table? Looks very cumbersome.

Or should I create a Availability table and link players and training sessions?

So it’s both a database question and a layout question.

Any help is much appreciated.

Cheers
Robbie

[Darren_Murphy]

@Rob_Cap

Or should I create a Availability table and link players and training sessions?

That’s probably the simplest approach. Every time a player registers for a session/game, add a row to that table. As a minimum, you need columns for PlayerID and Session/GameID.

You can then link that table via relations to both your Players and Schedules table.

[Rob_Cap]

Thanks for the hint. Please, be patient as I am a newbie.

How do I display a table or list in the layout with the round/date and then a switch or toggle that the user can check?

I think that that action will create a new row in the Games Availability table.

I can have a page called Game availability. The data source is the Game Schedule Table.
I need to put a checkbox next to each round. Can’t figure out how.

[Darren_Murphy]

@Rob_Cap

How do I display a table or list in the layout with the round/date and then a switch or toggle that the user can check?

I think that that action will create a new row in the Games Availability table.

You can’t do it with a switch, because switches don’t support actions.

What you can do is add a collection to your screen and set the Game Schedule table as the source. On that collection you can configure a Collection Item action for Player Registration. The action on that would be an Add Row that targets the Availability table, setting both the PlayerID (from User Profile) and the Game ID (from the clicked item).

[Rob_Cap]

Hi, I’ve tried to follow your suggestion, but clearly I’ve done something wrong.
I go step by step on what I’ve done.

TABLES

Users table, where I have:

• Player_ID (row ID unique identifier)
• Name
• Email
  
  

This table has the list of players

Games table, where I have:

• Game_ID (row ID unique identifier)
• Round
• Date
• Time
• Team (not relevant in this example)
• Games_details (bringing together all games details, relevant only for layout purpose)
• Availability (forget about this for the moment)
  
  

This table has the games calendar

Availability table, where I want to write availabilities for each player and each game. I have:

• Game
• Rel_game (Game relates to Games→??)
• Player
• Rel_player (Player relates to Users→??)
• Availability (boolean)
  
  

This table is empty.
I want to add a row each time a player gives availability (and modify it if players change their mind)

LAYOUT

Games page displaying:

Collection (list). Source data = games
where I display the games.

When Users click on a game a form slides in:

• Destination table: availability
• Game = Game (prefilled)
• Player = Player (prefilled)
• Availability (switch) = Availability

ISSUE #1: On submit I see that two identical rows are populated in the table Availability rather than one. Why is that?

ISSUE #2: Values in the Availability table. Here I’m lost and going trial an error.

• Game: the date of the games is shown
• Rel_game: Relate to items where the value in: Games. Matches the value in: Games → Date (then it shows the Round). Otherwise it does not show anything. Especially I don’t know how to link it to the Game_ID
• Player: the player name is shown
• Rel_player: Relate to items where the value in: player. Matches the value in: Users → Name (then it shows the Name). Otherwise it does not show anything. Especially I don’t know how to link it to the Player_ID
• Availability (boolean) is ticked.

ISSUE #3: The player has something come up and needs to change from available to unavailable.
The only way I managed to do it is the following:

• Create a layout to display availability
• Display the availabilities as table
  
  

On click on a ellipses at the end of row (which is not intuitive as there’s no indication that the table rows are editable and the ellipses are off screen at the end of the row), a form is open where the user can change availability.

This is recorded. However, as per ISSUE 1, I generated 2 rows and this action changes the first row only. So now I have a duplicate record saying available and unavailable.

ISSUE #4: this is super-cumbersome for a player.

I’d like to use a a checklist collection in the Games page, where a player can quickly tick the games he is available.
However, this cannot be recorded in the availability table. I could not make the add row action work.

This is why I added the Availability (user specific) to the Games table, but then I could not figure out how to copy it to the Availability table.

A lot of questions.
If I could get help on the data structure and on ISSUE #1 (duplicated row on creation), that would already be awesome.

[Darren_Murphy]

@Rob_Cap

ISSUE #1: On submit I see that two identical rows are populated in the table Availability rather than one. Why is that?

I suspect that’s because you have an onSubmit action that is adding a row. It’s a common beginners mistake. A form already adds a row when it is submitted, that is what it is for.

@Rob_Cap

ISSUE #2: Values in the Availability table. Here I’m lost and going trial an error.

You don’t need to use a form, because you already have the values you need. The PlayerID can be taken from the signed in users User Profile row, and the GameID from game that is being viewed. I would suggest adding a “Register” button to the Game details screen, then instead of opening a form, just use an action that directly adds a row to the Availability table.

@Rob_Cap

ISSUE #3: The player has something come up and needs to change from available to unavailable.

Here is what I would suggest:

• In your Games table, create a Query column. Ensure that “Match multiple” is not selected. Target it at the Availability table and apply the following filters:
  – GameID is This row->GameID
  – PlayerID is User Profile->PlayerID
• If that Query is empty, it means the signed in user has not registered for that Game. So you can use the status of that column to decide which option to give the user - Register or Unregister
• Create an if-then-else column:
  – If Query is empty, then “Register”
  – Else “Unregister”
• Use that if-then-else column as the label on your “Register” button.
  - Create a Single Relation column in your Games table that matches the GameID with the GameID in the Availability table. This column will be used to delete the related row when a user chooses to Unregister.
• Finally, modify the action on the button, including a conditional check:
  – If Query is empty (Register)
  — Add Row to Availability table
  – Else (Unregister)
  — Delete Row → via Query

[Darren_Murphy]

@Rob_Cap

ISSUE #4: this is super-cumbersome for a player.

I’d like to use a a checklist collection in the Games page, where a player can quickly tick the games he is available.

There are other ways, but it can get a little complicated. You could use a Table collection, which allows for inline buttons and associated actions. Below is a screenshot from one I created recently for a pool league that I’m involved in. Similar concept, players can register/unregister for scheduled events with a single click (in this case, it’s actually two clicks, because there is a confirmation screen).

If you wanted something like the above, I can help. But like I said, it can get a bit complicated.

[Rob_Cap]

Thanks so much, Darren.
Issue 1 is exactly what you say.
I’ll look into the rest.

[Darren_Murphy]

Actually, @Rob_Cap I realised that the below is wrong:

@Darren_Murphy

Create a Single Relation column in your Games table that matches the GameID with the GameID in the Availability table. This column will be used to delete the related row when a user chooses to Unregister.

Not only is that not guaranteed to match the correct row, it isn’t required.
All you need to do I make sure your Query isn’t set to match multiple rows, then you can delete the row via the Query.

I’ll update my original response.

[Rob_Cap]

Thanks again, Darren. I think it works now. I’ll do some testing tomorrow.
I’ll ask for the complicated solution when I am more comfortable with the platform.

[Rob_Cap]

This is exactly what I needed. I gave it a go and I got to this stage.
I added a button which adds a row to the Games_availability table.
It captures correctly game, player and the boolean ‘availability’ is ticked.
I added a status which displays availability (I added an IF THEN column to the games_schedule table).

I’d love the button to be dynamic as in your example, thus allowing a player to change their availability. How did you achieve that?
Thanks in advance

[Darren_Murphy]

• Create a Query in your Events table, and target the Availability table. Apply the following filters:
  – EventID is This row->EventID
  – PlayerID is User Profile->PlayerID
• Make sure you don’t check the “Match multiple” box in the query configuration.
• Create an if-then-else column for your button label:
  – If Query is empty, then “Register” (or whatever you use)
  – Else “Unregister” (or whatever you use)
• Now change the action on the button to a custom action, with two conditional branches:
  – If query is empty:
  — Add Row (as you do now)
  – Else:
  — Delete Row → Query column

[Rob_Cap]

I see why you say “it gets complicated”. Let me give it a go.
Thanks so much.