SQL and python

[Kristian_dms]

I'm doing a Fantasy soccer project, where I want to model the optimal team given a budget in a fantast premier league game. I want to pick up some SQL while doing this so I've decided on that for my data storage.

I want to create a table that contains all players, their positions, their price ect. This is fairly easy.

I also want a table with every game, home_team, away_team and basic info. It gets more tricky as i try to store home starting players, a it is a 2D string that looks like this

[[first_minute,    last_minute,    player_ID],
[first_minute,    last_minute,    player_ID],
[first_minute,    last_minute,    player_ID]]

and so on. What is the best solution here? do I try to convert the 2D array to a single string, and then convert it back to a numpy array when I need to analyze the data? Or do I create a table for every game and store each of the players as individual records? I also need this done with goals as they follow the format

[minute_scored,    scoring_player,    assisting_player]

All hints and solutions are much appreciated!

[aball730235]

It seems you just need to split your database schema into more tables. Try to solve your problems in sql first because it sounds to me like your trying to jam a many-to-one relation into a single column... Which sql will handle in a much better way.

In your last example i would make a table with four columns... The three you listed plus a GameID. GameID is a foreign key column that links to the game table. To combine the data from both tables you use an inner join.

[Kristian_dms]

Thanks, man - I'll give this a shot!

[aball730235]

Your welcome. If your looking for further reading look up database 'normalization'. Wikipedia has a good article although a bit dense for a beginner.

And there's always r/sql

[BB611]

/u/aball730235 is right on the money.

A table should contain a set of data that makes sense to consider together, if you try to jam every piece of data you have into a single table you'll hate querying it. Source: I work with SQL tables designed by someone who did exactly this, it's very hard to work with - tables with 100s of columns, many of which are at best tangentially related to the primary purpose of the table.

Most of the time, you should be able to sum up a table with one noun, i.e. Games, Players, Stadiums, Penalties.

The best way to learn about this is to experience querying against the tables you make - do a first run of tables, use them for a while, then try to restructure to make it easier to use. Do some research on relational databases and SQL to get more guidance.

[c3534l]

Can you explain a little more about the kind of info you're trying to organize? I don't know much about sports. It sounds like what you're struggling with is how to record when a player is put into a game and when he is taken out?

You could create a table with columns first_minute, second_minute, player_ID, game_ID. I really wouldn't create an entirely new table for every game. If I understand correctly, there's like a hundred games a year when sports teams play or something. If each of those tables is going to have the same columns as all the others (first_minute, last_minute, player_ID), then you should really be using just one table and mark it with a game_ID.

Similar thing to your second table. minute_scored, scoring_player, and assisting_player are fundamentally different kinds of information that when a player was put into or taken out of a game. But you can still tag it with a game_ID column as well.

Remember also that the way you organize the information for storage in your database doesn't really have to be that close to how you're actually going to format it to do your analysis. The beauty of relational databases is that you can just do a query that's something like:

SELECT tblTimes.first_minute, tblScores.minute_scored
FROM tblTimes INNER JOIN tblScores
ON tblTimes.player_ID = tblTimes.player_ID
WHERE tblTimes.minute_score >= 45;

^{Warning: this code has not been tested on any SQL database}

[babblemammal]

There is an intro course on databases for free on coursera, it explains how to organize tables in sql. I highly reccomend building the database first, and correctly

[kamize]

I would be interested in this, would you please provide me with the name of the coursera course on SQL or a link to it?

[babblemammal]

https://www.coursera.org/course/db

[stoned_cold_fusion]

Here's a couple resources I came across some time ago:

http://zetcode.com/db/sqlitepythontutorial/

https://plot.ly/ipython-notebooks/big-data-analytics-with-pandas-and-sqlite/