Table architecture advice 1

[G12Consult]

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

 

[Andrzejek]

I would start with what you stated and build on that. So, I would do:

[pre]
tblSports
ID Sport
1 Cricket[blue]
2 Field Hockey[/blue]
3 Soccer
4 ...

tblMetrics
ID SportID Metric
1 1 Runs
2 1 Wickets
3 1 Overs
4 1 4's
5 1 6's
6 1 Outs
7 1 Wides
8 1 No Balls[blue]
9 2 Goals
10 2 Short Corners
11 2 Long Corners
12 2 Penalties
13 2 Circle Entries[/blue]
14 3 Goals
15 3 Offsides
16 3 Yellow Cards
17 3 Red Cards
18 3 Tackles
19 3 Assists
20 4 ....
[/pre]
Then, of course, you need Teams:

[pre]tblTeams
ID SportID TeamName
1 1 Rolling Balls
2 1 Fighting Bats
3 1 Wicket Stumps
4 2 Running Sticks
5 3 Old Shin Guards
6 ...[/pre]

And players:

[pre]
tlbPlayers
ID PlayerName Age ...
1 Elvis 27
2 Bunny 17
3 Jones 20
4 ...[/pre]
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.

 

[pjw001]

That looks like a pretty good solution.

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.