otherDates: jsonb('other_dates').notNull().$type</* This type can be defined and exported in types.ts */ {
label: string;
fromDate: number; // Timestamp in milliseconds
toDate?: number; // Timestamp in milliseconds
}[]>().default([])
The current dates column would be removed.
Create the appropriate indexes, for this, keep in mind we might provide future functionality for the user to filter by published tournaments, concluded tournaments, tournaments that have its player regs. open and tournaments that have its staff regs. open. If the indexes are to heavy for the tournament table, we might consider moving these dates into some sort of tournament_dates table with a 1-to-1 relationship with tournament.
Why?
Moving the dates found in the
dates
JSON field into their own columns will make it so we can index by those dates and also make querying a lot easier.How?
Move the JSON properties into their own timestamp column:
publish
->publishedAt
(published_at
)concludes
->concludesAt
(concludes_at
)playerRegs.open
->playerRegsOpenAt
(player_regs_open_at
)playerRegs.close
->playerRegsCloseAt
(player_regs_close_at
)staffRegs.open
->staffRegsOpenAt
(staff_regs_open_at
)staffRegs.close
->staffRegsCloseAt
(staff_regs_close_at
)All fields are nullable, with no default value.
As for the
other
property, we can map it as such:The current
dates
column would be removed.Create the appropriate indexes, for this, keep in mind we might provide future functionality for the user to filter by published tournaments, concluded tournaments, tournaments that have its player regs. open and tournaments that have its staff regs. open. If the indexes are to heavy for the
tournament
table, we might consider moving these dates into some sort oftournament_dates
table with a 1-to-1 relationship withtournament
.