Database schema design for Tournament Stage and related

[yongshun950824]

From the bracket-model design

Stage - ID, TournamentID...
Group - ID, StageID
Round - ID, StageID, GroupID
Match - ID, StageID, GroupID, MatchID
MatchGame - ID, StageID, MatchID (Parent ID)

My MSSQL database design

Stage - (running) ID, TournamentID...
Group - (running) ID, StageID
Round - (running) ID, StageID, GroupID
Match - (running) ID, StageID, GroupID, MatchID
MatchGame - (running) ID, StageID, MatchID (Parent ID)

My IDs were implemented with INCREMENT(1,1) in SQL which means the IDs are incremented automatically.

Sample Data in DB

I have an "issue" that when importing data with

await window.bracketsManager.import(data)

The data I query from window.inMemoryDatabase.data

The IDs will be reset-ed and started with 0.

This is hard for me to update match and match_game. As the Match ID in DB was unmatched with Match ID in MemoryDatabase.

Question:

1. Is there any way that allows memoryDatabase to use the imported IDs instead of re-generate on its own?
2. If Q1 is not possible, any suggestion/advice for the database schema design?

Thanks in advance.

[yongshun950824]

Side note: The Tournament Stage in SQL database is based on the data generated from brackets-manager. For each change of Tournament data, I will remove all the Group, Round, Match, MatchGame from database first and next insert the new data.

Suggested "trick" refer to: #124 (reply in thread)

[yongshun950824]

Thanks for the assist. Anyway, I think I found the "trick" to get the match and match game id for the update.

To get the match, what I did the trick is getting these values:

• groupId: (DB data) group's number - 1 (id after normalised is started from 0)
• roundNumber: (DB data) round's number
• matchNumber: (DB data) match's number

And apply this method:

brackets-manager.js/src/base/getter.ts

Line 474 in 83a875a

protected async findMatch(groupId: number, roundNumber: number, matchNumber: number): Promise<Match> {

While for match game also done the similar trick:

• parent_id: match.id get from above method
• number: (DB data) match_game's number

brackets-manager.js/src/base/getter.ts

Line 497 in 83a875a

protected async findMatchGame(game: Partial<MatchGame>): Promise<MatchGame> {

brackets-manager.js/src/base/getter.ts

Line 504 in 83a875a

if (game.parent_id !== undefined && game.number) {

[Drarig29]

Ok, sorry I couldn't work on it. Nice that you found a workaround in the meantime

[Drarig29]

I had a look at my tests and I remember I wanted to do this to be sure that all the IDs were consecutive and starting from 0.

Mostly, I have some parts of the library assuming IDs are consecutive. So I'll let this normalization feature for users to be able to reset their data if they want and that they don't have another source of truth that they need to sync (like you).

I think I'll just make this normalization feature opt-in.

[Drarig29]

Fixed here: https://github.com/Drarig29/brackets-manager.js/releases/tag/v1.4.1

[yongshun950824]

Nice, thanks for spending the weekends to make this enhancement.