I am new to MySQL, but after reading for a weekend, it is apparent to me that design of the database is a critical step.
I have a Folding@Home team of about 500 members. Each member's stats include the member's name, the points accrued, the work units completed, the member's rank within the team, and the member's rank in the world.
A stats update is issued eight times a day (every three hours) by the F@H group at Stanford University as a text file, which I can retrieve and deal with using Perl (if you're interested, the file I'll be retrieving is at
http://vspx27.stanford.edu/teamstats/team63.txt). I've been tracking stats on this team for several years now, and I maintain a website of stats about each member, but I've been using flat-file (text) databases, and I'm exploring a switch over to a MySQL based system.
The questions I will ask of the database (for each member, and separately for the team as a whole -- the team aggregate stats are provided by Stanford along with the individual member stats, thus the team is sort of like a super member, but should be maintained separately) include such things as :
1. Each member's current points awarded, work units completed, team and world ranks.
2. Each member's change in points total, work units total and team and world ranks over time -- say per day, per week and per month, thus I'll need to retain historical data spanning about 30 days for each member (240 updates at 8 per day).
3. Whether (and when) a member crosses any of a number of thresholds (currently, we're using 1,000, 2,500, 5,000, 10,000, 20,000, 50,000, 100,000, 250,000, 500,000, 750,000, and 1,000,000 points; thus when a member's point level is >= 1,000 and < 2,500, he or she is a member of the "1,000 points club". When he/she crosses the 2,500 point level, they become a member of the "2,500 points club" and cease membership in the 1,000 point club, etc.).
4. Several date/time related queries, such as when a member joins, when his point total last changed (many team members participate for a while then stop so, even though they continue to appear on the team list, their point total doesn't change -- I need to be able to differentiate "active" members from "inactive" members), when a member achieves the thresholds outlined above, the date/time of the latest stats update, and the like.
My question is how to best design such a database? Put all member stats in a single table keyed on member name, and have a separate table for each "club"? Have separate tables for member's points, work units, and ranks, etc., all linked by member name? Maintain current stats and historical stats separately or together?
I'm fairly confident I can work out the Perl/MySQL programming needed to retrieve the answers to such questions if the database is correctly designed. My feeling is that getting the database design correct from the start will save me a lot of headaches later on, but I have no experience to draw on. Any suggestions will be greatly appreciated...
Mike
Start Free Trial
