Database table naming

[kj]

Just thinking about how to keep table naming consistent.

Tables

For the teams/league tools, we may have tables like:

player (full_name, gender, birth_date, ...)
team (name, ...)
team_roster (league_id, team_id, ...)
player_x_team_roster (player_id, team_roster_id, position ...)
league (start_date, end_date, days, ...)

So there would be players, who can play in many leagues and teams. Players and teams persist between leagues. For each league, a team will have a new team roster. The join table player_x_team_roster joins players to a team roster for that league. This naming table1_x_table2 would be used for all join tables.

How would fill-ins work? Maybe a team roster is not associated with a league, but each tie? Or perhaps a team roster stays associated with a league (for consistency, this might be called league_team_roster, but is only used for reference and maybe suggestions during player selection for a tie, but the teams for each tie could be completely arbitrary, eg.

tie (tie_t1_id, tie_t2_id, ...)
tie_team_roster (t1_player1_id, t1_player2_id, ...)

There might be a "lock teams" option for leagues which would prevent mixing and matching players like this, and would only allow players on the team roster.

Join tables

table1_x_table2 or xref_table1_table2 or xr_table1_table2

It's also worth mentioning for join tables, that you can do a compound primary key with the PRIMARY KEY table constraint like this:

CREATE TABLE player_x_team_roster (
    player_id INTEGER,
    team_roster_id INTEGER,
    PRIMARY KEY (player_id, team_roster_id)
);

Indexes

ix_table_fkey

Dates

something_date or something_at

I prefer the former, as start_date and birth_date sound better than starts_at and born_at, and starts_at implies the date is yet to come (likewise started_at). These should be UTC timestamps. If we need timezones, we can always add a timezone field to league.

Booleans

is_something