mysql table structure help

[saif7463]

Hi everyone,

Currently I have a table which stores user information and another table that has calendar events. For each calendar event, there are a variable number of rounds (ranging from 2 to 6) in which users earn points. I'm not exactly sure how to store this information.

Should I just create another table that has columns {userid, eventid, round#, score} to store the results? But then it is somewhat difficult to determine which rounds a particular user missed, (which I suppose I could get around by making a fake user who has a record in every round).

Or, should I just add more columns to the user table? But in doing so, I wouldn't exactly know how to loop through these columns as rounds of events.

Any help would be appreciated.

 

[gomarc]

Maybe it would be best if you explain a little more what you want to accomplish with what you have so far.

The community will probably give alternative solutions, and you can chose the best one for you.

 

[saif7463]

Yeah, sorry. I'll give a concrete example of what I'm trying to store.

So there are members of a team that participate in several competitions. The first competition has 3 rounds. Each round is scored out of 6 points. Most members of the team participate in the competition, and thus have 3 scores associated with the competition. So like, Bob scores 3 points, 5 points, and 6 points on the 3 rounds, respectively (Other members of the team also score some number of points ranging from 0 to 18 total points). Then, a few weeks later, there is another competition with 6 rounds, each round worth 6 points. Again, most members of the team participate, with each member procuring points in the 6 rounds, e.g. Bob scores 5, 6, 3, 1, 0, and 6 points.

The main purpose of the storage is to allow members, like Bob, to access how he did at different meets and compare his scores with other members of the team.

At the moment, I have a table with member information (userid, name, email, pass, etc.). And I also have a table of the competitions, or event information (eventid, name, date, description, etc). I just don't know how I should store Bob's scores from the 2 (and counting) competitions.

What I'm thinking is something like, table "results" with columns: scoreid, userid, eventid, round#, score. And in Bob's case, this would translate to the rows(assuming his userid is 1, and the competitions have id's 0 and 1):

0, 1, 0, 1, 3
1, 1, 0, 2, 5
2, 1, 0, 3, 6
3, 1, 1, 1, 5
4, 1, 1, 2, 6
5, 1, 1, 3, 3
6, 1, 1, 4, 1
7, 1, 1, 5, 0
8, 1, 1, 6, 6

 

[descalzo]

Does you event table include the number of rounds?

You don't need a score id.
[userid , eventid, round] uniquely identifies the entry so you make that your primary key.

 

[xadrieth]

Um, I think you've got the right idea of what you want, but why do you have some 0's inserted into the sample data table where they should not be?

 

[saif7463]

Does you event table include the number of rounds?

You don't need a score id.
[userid , eventid, round] uniquely identifies the entry so you make that your primary key.

Yeah, I plan to have the number of rounds in the event table.
Ooh, so in say phpMyAdmin, I just select the radio buttons for primary key for those 3 columns?

So I assume the above system would work. But, what if I want to have the rounds be named (primarily for the members to better identify the rounds)? Like instead of just 1, 2, 3, they were "Algebra I," "Geometry," and "Algebra II." Should I just introduce another table, "rounds" with columns eventid, round#, name and keep the result table the same?

Thanks for your help!
Edit:

Um, I think you've got the right idea of what you want, but why do you have some 0's inserted into the sample data table where they should not be?

The 0's are for the competition with id 0. It seems fine to me the way I have it.