Football Stats Database Design Q

davestar_delux · 3 Nov 2008 at 18:31

Hey, I'm trying to make a football stats site for my team. Just working out how to get all the info in a database, obviously I will have a Players table with a record for each player and a Fixture table but I'm not sure how to store things like each player that played in a fixture as it would need 11+ entries? I would probably need a 3rd table called Squad with just a list of player IDs linking to each player? And also goals and the time they are scored which would allow me to search and filter based on say all games when we have scored in the last 5 mins or a certain player has scored a hat-trick?

Haircut · 3 Nov 2008 at 18:57

I was looking at doing something similar a while ago.
I never got very far with it though due to lack of time.

I did produce a database diagram for it though, which may be of use to you.
It's possibly a bit more complicated that you need, so remove what you don't need. You may want to denormalise some things as well, e.g. putting a team id in the goals table as using that model it's a bit of a pain to actually find which team a goal is for!

JamesU2005 · 3 Nov 2008 at 19:50

I know a lot of non-league clubs use tplSoccerStats.

suarve · 4 Nov 2008 at 16:24

Haircut said:
I was looking at doing something similar a while ago.
I never got very far with it though due to lack of time.

I did produce a database diagram for it though, which may be of use to you.
It's possibly a bit more complicated that you need, so remove what you don't need. You may want to denormalise some things as well, e.g. putting a team id in the goals table as using that model it's a bit of a pain to actually find which team a goal is for!

Just outta interest, what did you use to create that design. I'm forced to use SSADM at the minute, but that looks way better.

Pho · 4 Nov 2008 at 16:37

Looks like Visio

Haircut · 4 Nov 2008 at 17:46

Yep, it was done in Visio.
One of the benefits of an MSDN subscription