I'm creating a site that gives results to highschool athletic games as well as the game summaries and statistics. I have everything working except for the statistics. I've tried a few different possibilities that work, but are inefficient.
For example take baseball:
I could create one TABLE tblStats and have the following fields:
RosterID,ScheduleID,StatName,StatData,Place
RosterID = link to roster table with player info (name etc.)
ScheduleId = link to schedule table (date, location etc.)
StatName = Name of statistic (ie: Bat Avg, Homeruns etc)
StatData = data for stat (ie: 34 <for homerun>)
This is very generic, but the data entry person must enter both the statname and statdata every time. And if I want the data to be in a certain format, be expandable, and be able to sort. It doesn't work very well after using this for the rest of the sports together at the same time.
Then I decided to try a different table for each sport cause that allowed me to specify stat names, ranking order etc. However this way involves me having to manipulate strings and comma delimited strings in ways that are inconvenient to update.
What I'm trying to say is if you have to do the following with the data how would you go about designing the database:
Must Be In Certain Format (That is changable ie: 15.59 sec, 1:34.43, 14-6 feet, 45 homeruns)
Must Be able to sort data correctly (ie: Track Races order low->high, homeruns -> high to low)
<b>Is there any way to sort text by number sort like a CInt(num) in SQL?</b>
Must be able to accept new stats for a specific sport (ie: now accept RBIs for baseball) and be able to accept a whole new sport with it's own stats (ie: startup lacross)
Nice to have the capability for total drop down entry? (ie: 15.24 seconds -> three drop downs for seconds, millisecs, and data type)
I know this is a lot of information and almost impossible to understand what I'm trying to ask, but maybe if someone knows alot about database design they could help me out and email me at stevemt@hotpop.com.
Thanks,
steve
