Advice needed on planning a Revo site's database structure

Agreed. There’s something not quite right about my approach, but I can’t pinpoint it. What you said about splitting by week could work, but that doesn’t seem quite right either. Any other ideas or know anyone else I could ask? I’m getting a little desperate.

OK, how’s about this

user table -> user_years table joined on user id
user_years table has columns user_year_id, year, user_id
Then you have a table for each week (you say the number is set in stone)
This would have a user_year_id as a foreign key so you could relate back to user and year
In each week table you would have columns week_id, user_year_id, stat_id, stat_value
with a lookup table to hold all the stat types with columns stat_id and stat_name

Will make your joins a little more complex but will only store the info relevant to each user, no more, no less.

Thanks, @bluefrog! This is the kind of help I needed. I like this in terms of organizing the user’s stats, but I think (and correct me if I’m wrong; I suck at DB schema) it reintroduces a problem on some of the other page views, which will aggregate stats from multiple users, like this:

http://dl.dropbox.com/u/2180905/topstats.jpg

Or maybe not...I don’t know...my brain is melted at this point.

Hmmm...I may have figured something out that hadn’t even dawned on me. See the tab that says My BIGs? THAT is the info that’s getting aggregated and compared against other users, and it doesn’t have the added complication of being tied to a week. Its entry screen looks like this (sorry for not showing this sooner):

http://dl.dropbox.com/u/2180905/mybigs.jpg

I think I may have been trying to solve a non-existent problem. It doesn’t really matter how I store the week-by-week stats, as only the BIGs stats are getting compared. I could probably even just use extended user fields. Or maybe it’s a good idea to future-proof this and store it in separate tables per your suggestion, just in case they want to aggregate them later?

Again, I’ve been staring at this for way too long, so if I’m totally missing something, I’d love for someone to tell me.

Nothing jumps out at me there that you shouldn’t be able to pull out with the right query - maybe if you point me at some specific issue I can think it though, brain not firing on all cylinders at this time of the evening myself

Yeah, if I’m just saving the My BIGs data, I think I can do a single table, with one row per user and a column for each stat (I’ll probably still save to separate tables for each state to cut down the overhead a little). Since the user’s week-by-week stats are only going to be shown on their individual profile pages, I think I can store them however I want. The only issue would be down the road if the client wants to do week-by-week comparisons of users...hmm...

Regardless, I think I have a short-term solution, and some ideas for a better long-term solution. Thanks! If anyone comes up with just an absolute brilliant way to do this that hasn’t been mentioned yet, please let me know!

There is still some kind of inconsistency here because the bigs seem related to week on the last page you showed me yet there is nothing on the big entry interface that makes that obvious so you need to make sure you take account of that.

Oh, crud. Yes, the second-to-last link I sent does compare weekly. Looks like I’ll have to do this after all.

Care to take a swing at the PDO syntax for your suggested setup? ;-)