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)
Seb : So game 1 is now available again for a new draft because I deleted completely the first draft. This is just like starting over.
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.
Seb : Exact, so as you can see, it’s different then soccer where the league say this date and the association (club) find field/time. In hockey, the association give timeslots to the league, the league use them to schedule matchups on field/date/time and the game is available now to association to modify field/time. Richard : never ever possible for the association to change the date on a project game?
NO. Never in project (draft), since we are taking their time slots, if a association realy need to change a date of a draft game, he will call the scheduler of the league by phone.
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. - Rich : ok
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. - Rich : ok
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. - Rich : ok
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.
Rich : The user is block to enter manually the game number, since the next game number is in the schedule table, that will prevent all possible errors.
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.
Rich : This option is available only if all the games in the schedule is deleted.
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.
Rich : for me this option should not be available, stick with one sequence like 1 to 999, keep it simple, because if this is available, there are a lot of creative users that will fuckup the system.
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.
Rich : If the sequences is exhausted, stop publishing the games and tell the user that is sequence is to small, that should be done before publishing, since you know how many games you will published and you know your sequence, that will give a chance to the user to do some corrections before publishing.
After the games are publish it is more trouble to change that and falling back on the game ID will produce a bad sequence for people two follow.
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
Rich : Since the next game number is in the schedule table, this problem should never append, the user that does the scheduling will never create manually a game beetween a draft, he will maby create a few games before producing the draft, but since the next game number is in the table, the problem is resolved.
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?
Rich : The only time you reset the sequence, is on deleting all the games of a schedule, otherwise forget the game number that was deleted, go with option 2
Seb : If possible, both options should be available to the users. If we have to choose (to keep it simple), it should be option 2 because it will show a user that there was a game 1250 and it’s not there anymore.