Table architecture advice
Table architecture advice
(OP)
Hi,
I have been tasked with creating a database used to store statistics for various sports. My issue lies in that each of the different sports have different measures or metrics you would store in a database.
For eg.
Cricket
Metrics - Runs, Wickets, Overs, 4's, 6's, Outs, Wides, No Balls
Field Hockey
Metrics - Goals, Short Corners, Long Corners, Penalties, Circle Entries
Soccer
Metrics - Goals, Offsides, Yellow Cards, Red Cards, Tackles, Assists
Metrics can either be player or team
What would be best practice in setting up the tables? Would I use one table and just have loads of custom fields for available metrics or have a seperate table per sport?
Player data may be like
Player A - Goal - 15 mins
Player A - Assist - 18 mins
Team Data
Team A - Goal - 15 mins
Team B - Goal - 20 mins
Thanks in advanced
I have been tasked with creating a database used to store statistics for various sports. My issue lies in that each of the different sports have different measures or metrics you would store in a database.
For eg.
Cricket
Metrics - Runs, Wickets, Overs, 4's, 6's, Outs, Wides, No Balls
Field Hockey
Metrics - Goals, Short Corners, Long Corners, Penalties, Circle Entries
Soccer
Metrics - Goals, Offsides, Yellow Cards, Red Cards, Tackles, Assists
Metrics can either be player or team
What would be best practice in setting up the tables? Would I use one table and just have loads of custom fields for available metrics or have a seperate table per sport?
Player data may be like
Player A - Goal - 15 mins
Player A - Assist - 18 mins
Team Data
Team A - Goal - 15 mins
Team B - Goal - 20 mins
Thanks in advanced
RE: Table architecture advice
Then, of course, you need Teams:
And players:
And since any Player can participate in one or more Teams, you will need a junction table to know who plays in what Team.
and Normalize the heck of it.
You cannot go wrong with properly designed fully normalized referential data base.
Just my opinion.
---- Andy
There is a great need for a sarcasm font.
RE: Table architecture advice
The only thing I would add is that having a "sequence number" on some of the table (tblMetrics in particular) would help controlling the sequence in which the data is presented.