Requirements
Each league/tournament/etc allocates sequences of game numbers to each schedule
Game numbers are only unique within each schedule and may be reused in other schedules
Game numbers often have a prefix and suffix to identify which league and category
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)
Draft games can have their numbers re-sorted based on the date/time prior to publishing, but published games cannot be re-sorted
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
Existing behaviour
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.
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.
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.
If a numbering sequence exists in the schedule, the user is not given the option to manually enter or change game numbers in the schedule as they will be automated. This will prevent potential user errors.
When a game is deleted, creating a gap in the sequence, that number in the sequence will not be reused, despite the gap.
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. All games in the schedule must be deleted in order for the reset to be available.
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, a validation error will prevent games from being created or draft games from being published until a viable sequence is available. We cannot fall back to a game ID as that would be a bigger mess to clean up, and it would be easier to block publishing so that they can fix the sequence then resume publishing.
Assuming a sequence of U13L1000-1999, it is possible for a user to create 100 draft games, which will use sequence numbers 1000-1099, then before publishing, a user may create a published game in the same scheduling, which will use the next number in the sequence, 1100. Publishing the pending draft games would retain their original 1000-1099 range of game numbers. Publicly, a gap in the game numbers would appear as 1000-1099 hasn’t been published yet, but this is not a concern.
Examples
PCAHA game numbering system is well defined: