Substitution Event needs to FK to GamePlayers

[rogersmark]

Right now they FK to Players instead of GamePlayers, and that's poor. Also need to make use of this model in general, but some mls-scraper work needs to be done first. That work is documented here

[dget]

Hey @F4nt, I'm trying to get a sense of what the different things in the DB schema are supposed to be.

Why would different models FK to GamePlayer instead of separately to Game and Player?

My gut says you'll most often want to query against one or the other, and not as often for the game-instance of a player (with the biggest reason I see for that being to fetch the statline for a player in a game). Neither access pattern is impossible either way, but it seems like it'll be cleaner to do Game and Player.

[rogersmark]

Hey @dget,

The main thing that ended up driving that is that players often play out of position and I wanted to capture that. For instance, Logan Pause is a central midfielder but played at RB for the Fire last week against Montreal. It wouldn't make sense to change his position on the Player object, because that should be (mostly) constant.

I wanted the flexibility to be able to filter down and see games where Pause played RB, and be able to see what affect that may or may not have had on outcomes. Having Player and GamePlayer lets us use Player as a constant of sorts, perhaps for displaying rosters, while GamePlayer can be used for game day stats line as you mentioned.

[dget]

That seems fair. For something like substitution, do you think it should have an FK to both Game and GamePlayer, or just GamePlayer? I'm thinking just GamePlayer, but noticed Goal has both, and want to make sure whatever I end up adding is consistent.

[rogersmark]

Good question. The reason Goal has both is to simplify the query to get the totals for displaying the score. Admittedly, that isn't really necessary, and I could aggregate the totals through the GamePlayer model, but it felt cumbersome and messy. Thus the FKs to Game and GamePlayer. I could really go either way on it though, as it does border on extraneous.

I'd think with Subs we could just FK to GamePlayer. I can't think of any great benefit to FK to Game as well, and it sounds like your thoughts on similar there. That said, if you can think of a benefit of FK to Game, I'm open to it.

By the way, if it's easier, feel free to email me anytime at: f4nt AT f4ntasmic DOT com.