×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Table architecture advice

thread183-1804371

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

RE: Table architecture advice

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

tblSports
ID   Sport
1    Cricket
2    Field Hockey
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
9     2      Goals
10    2      Short Corners
11    2      Long Corners
12    2      Penalties
13    2      Circle Entries
14    3      Goals
15    3      Offsides
16    3      Yellow Cards
17    3      Red Cards
18    3      Tackles
19    3      Assists
20    4      ....
Then, of course, you need Teams:

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   ...

And players:

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



---- Andy

There is a great need for a sarcasm font.

RE: Table architecture advice

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close