database restructure?

[russellcain]

I feel like im straddling a properly flat struct, but need to lean into it.

• Ex: I think I need a league-team ref table.
  • Current flaw:
    • if someone altered the url to add player x to team y in league z, but team y was created into league a, we'd have no way of confirming and would just stick row (z, y, x) into the db.
    • which is whack because then when i fetch the teams in league z, pop! there appears new team y with now just one? player. This would be a nightmare.

I think I want to go back to reading some different db design ideas; the flatness feels silly when the data is this straightforward of a relationship, but i guess it is okay. I'd prefer this division of data over updating multiple tables when something is added or deleted.

[russellcain]

Is there anything to be said for the idea of the team_id being a concatenation of a new int and the league_id? or the league id followed by a random string/uuid? then we can enforce uniqueness in the teams table and have that relationship embedded? it's a given that a team will never get "shifted" to another league, so this coupling seems like a fair dependency. But this is rife with its own set of muddling issues. I think a separate flat table is the way to go. I just don't like and believe it fully yet.