0

I've been attempting to learn how to build a database of historical sports statistics using postgresql/sqlalchemy ORM the past few days and could really use some advice on approach. My use case seems to be a bit different from examples I've read through and maybe that's why I'm having trouble. FWIW I've worked with this type of data for years in Excel and later python/pandas. I guess I'm just so used to the way of going about things within those constructs that sqlalchemy has my brain all twisted up. Below are some general guidelines/questions and I will add some examples of my mapped classes soon.

  1. Mapped classes for schools (~360), their respective conference (~15) and players on their roster (~5000) from 2010-present. Many more beyond that but trying to be somewhat brief.

  2. Schools can change conferences and players can change schools in between years. From an implementation standpoint do I need to have separate rows for conferences, schools, players for each year or would that be viewed as redundant?

  3. The data that I'm attempting to use comes from multiple sources with different ids that need to somehow "connected" either before adding to the database or preferably within. Below is a general example of data that would be added. I do have some flexibility surrounding what is or isn't inserted after parsing responses from the web but have been trying to keep that to a minimum.

espn = {"name": "Duke", "id": 4930, "venue_id": 5890, "venue_name": "Cameron Indoor Stadium"}
ncaa = {"name": "Duke Blue Devils", "id": 165646, "wins": 24, "losses": 10}
  1. When all is said and done I would like to be able to retrieve specific segments of data either as is or compiled. ie. statistical averages for players on Duke roster for games played at their home venue during the 2013 season.

Hopefully this at least makes some sense and it doesn't seem as though I'm looking for someone to drive the car for me; I could just use a few helpful pointers to get me in the right direction. Thanks in advance for any thoughts/suggestions.

Improve this question

1 Answer 1

Reset to default
1

Some pointers for you;

  1. Create entity tables for School, Conference and Players, with id primary keys for each table (ie school_id, Conference_id, Playersid).

  2. You can create a school-to-conference mapping table, and players-to-schools mapping table.

  3. You could create a Sources mapping table, with a source_id primary key column for each of the sources you have and load in their respective rows as you have described.

  4. Then just query for the data you need via SQL across these tables joining for the relations you're looking for.

Improve this answer
2
  • Thank you for the response. It makes sense for the most part but the one question I have pertains to #2. Would I need to incorporate a year/season primary key? Or some sort of composite key? For example, let's say I have two rows that need to be inserted. 1: {player_id: 1656, school_id: 753, season: 2022}, 2: {player_id: 1656, school_id: 261, season: 2023}
    – Nick
    Mar 10 at 23:02
  • Yes, you could do that, if you are sure the combination of player, school, season will always be unique. You could also add a foreign key relation to a Season table, referencing a season_id, if you need more complexity on the seasons definitions.
    – yeatsie
    Mar 11 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.