Requirements
Each league/tournament/etc allocates sequences of game numbers to each schedule - Rich : ok
Game numbers are only unique within each schedule and may be reused in other schedules - Rich : ok
Game numbers often have a prefix and suffix to identify which league and category - Rich : ok
A schedule with groups/flights will have a sequences per group (flight 1 will have 1000-1999, flight 2 will have 2000-2999, etc within the same U13 schedule) - Rich : ok
Draft games can have their numbers re-sorted based on the date/time prior to publishing, but published games cannot be re-sorted - Rich : ok
Renumbering is a manual action. Automatically doing so may cause confusion as people use numbers as a reference while scheduling, so it’s usually only done in preparation for publishing
Richard : Could you describe the meaning of : groups/flights do you mean pool/section within the same category (schedule)
Seb : Yes group = flight = pool = section = conference
Currently we have a (published) games table and a drafts table, where drafts essentially act as isolated containers of a schedule.
When creating a (published) game directly, if you do not specify a game number, it defaults to the system ID (which is a standard incrementing postgres sequence). Otherwise the game number specified is used as is.
When creating drafts, you must specify a starting game number, which will automatically increment within the draft, with an option to re-number the games (sorted by date). The draft’s starting game number option only exists within the draft, and ceases to exist once the draft is published.
When publishing the draft, if a draft game matches the game number of a published game, the published game will be updated instead of creating a new game.
Rich : This way of doing is questionnable, before i read the Proposed behaviour.
Proposed behaviour
Following the implementation of SPR-453 Draft Redesign, two game tables will exist: Game (published) and DraftGame (unpublished). The original Draft table that creates an isolated container effect will no longer continue to exist. Sequences (this proposal) are necessary to introduce to maintain the original start number draft option functionality.
Rich : in my language DratfGame means Project
For me the game number is just a reference number.
Each schedule has the next game number, so when you publish the draft schedule, it’s sort by date-time and assigne the game number, the last game number + 1, is update in that schedule table.
The next game you create Draft or not takes that game number and update + 1 for the next game.
When a game is cancel, you don’t touch that game number.
It will happen at the beginning of the year, that a schedule is Draft → Published → Cancel → Deleted completely → League add or remove a team → ReDrafted (in that case, the game number is reset by the user at is original first number)
When a game is Deleted completely the time slot associated with this game is liberated and available for a new game.
Keep in mind that Game (published) and DraftGame (unpublished) are available for the league and association scheduler. When the league establishes the draft schedule, all associations must see these games in their scheduler and if the league has given permission to be able to modify the games in drafts, they can only change the time and location. This is only done when the league has completed the project, this allows the association to maximize their ice for Referees, scorekeepers, etc.
A sequence will be composed of a starting number (which may have a prefix/suffix matching the regular expression /^(.*?)(\d+)(\D*)$/) and a maximum range.
This is modelled after Postgres sequences, which allows keeping track of the next number in a sequence within limits. This would allow you to create a sequence with a starting number of U13L1000 which increments by 1 up to U13L1999.
Creating a game, regardless of whether it’s published directly or a draft game, will pull the next sequence number available. Drafts will require sequences to be configured in the schedule as draft games cannot rely on the game ID sequence.
If the next sequenced number already exists for whatever reason (e.g., that number was manually provided when creating a game or in case of an error updating the sequence), the next available sequence number will be produced until a non-conflicting number is available.
It will be possible to manually reset a sequence back to a given starting number, which may be necessary in case a batch of games are deleted. Resets will never be done automatically as we cannot predict if the user wants to reuse the sequence or not.
It will also be possible to produce multiple sequences, so that you can have multiple sets of sequences as necessary. The sequence with the lowest next number that has not been exhausted will be used to produce the next sequence.
Once all sequences are exhausted, published games will fall back to using game IDs if there are no viable sequences available, and draft games will fail to produce.
Potential concerns
Assuming a sequence of U13L1000-1999 for simplicity of explanation:
It will be possible for someone to create a published game, which would use U13L1000, then generate 100 draft games which would be numbered U13L1001 through U13L1100. Before publishing these draft games, someone could create a directly published game without using a draft, which would use U13L1101 as the next number in the sequence.
At this point there are only 2 published games, 1000 and 1101, while 1001-1100 are unpublished.
Likewise, if you were to create another draft game at this point, the next draft game number would be U13L1102.
When publishing games, this would most likely cause a shift in game numbers due to the already published games
Deleting a game would create a gap in the sequence. Should this gap be automatically identified and reused or ignored?
You could publish games 12000 through 1299 and then delete 1250. If you generate 3 additional games, should the next games be numbered as:
option 1 : 1250, 1300, 1301, or
option 2 : 1300, 1301, 1302?
Seb : If possible, both options should be available to the users. If we have to choose, it should be option 2 because it will show a user that there was a game 1250 and it’s not there anymore.