Add table for historical conference affiliation

sprx97 commented 8 years ago

Needs to be a separate table, that indicates the year that each team joined/left a conference/division.

jkeena88 commented 8 years ago

I think the table structure you emailed would work best. Does that answer your question about how to handle teams that change multiple times?

1978+ is fine.

I don't know of a good source. I'm happy to manually update each offseason (and provide the starting list) if there's a relatively easy way for me to do that.

sprx97 commented 8 years ago

If you can find me a list from 1978 onwards that'd be great... Its easy to find the affiliations today, but historically is a bit harder. Worst case I can use wikipeida I guess.

jkeena88 commented 8 years ago

I'll put a list together

jkeena88 commented 8 years ago

What do you think is the best way to handle conference name changes (e.g. Pac 10 to Pac 12)? It could be the same conference ID (if we have a conferences table) and then just change the name, but then we lose visibility into name changes if we just overwrite those.... I don't know if that really matters I'm just typing while I'm thinking. Another option is to write them as if they're separate conferences (e.g. USC moved from the Pac 10 to the Pac 12 in 2011 or whenever), but I'd want some kind of way to tie those two conferences together. I don't know.

jkeena88 commented 8 years ago

If we're trying to skimp on details this could actually get confusing in cases like the Big East/American. The Big East legally turned into the American conference, but it was a vastly different makeup of teams. Plus even though they're legally the same conference a different conference is now called the Big East because they bought the name.

sprx97 commented 8 years ago

Hmmmm I think we have to handle these on a case by case basis.

In the case of the Pac, I probably would just have it in the table as "Pac", and assume that Pac 8/10/12 was the same conference and the numbers don't matter.
In the case of Big East/American, I think in most peoples eyes the "Big East" folded, and the "American" is a new conference, so all teams moved. I'd probably just have that as a mass-transition year where a bunch of teams leave the BE for the American and then a bunch of new teams "join" the Big East.

Thoughts?

jkeena88 commented 8 years ago

Yeah so far I like your suggestion. I'm working on the list today so I'll let you know if I run into any more troublesome ones.

sprx97 commented 8 years ago

Cool. Will you want full conference names or abbreviations fine (Southeastern Conference vs SEC)? We won't have to be pulling this from a datasource, so it should be totally up to personal preference.

jkeena88 commented 8 years ago

Abbreviations are fine in most cases, but for some like Sun Belt I don't want it abbreviated. I can send you a list of what I'm using if you need it.

sprx97 commented 8 years ago

Yeah that'd be good. I'll need one table to map conference names to IDs, and another to map teams to conferences.

sprx97 / JCS

Add table for historical conference affiliation #2