Challegnes Site Database Help

[System_Override]

Hi All,

 

I am working on creating a website that consists of challenges for users. Where I'm having a problem is I'm working on the part where there user's status of a challenge is stored. Here's what I mean...

 

Currently there are 2 tables in the database, Challenges and Users.

 

Users: ID, Username, Password, Email

Challlenges: ID, Title, Category, Description, Question, Answer, Difficulty

 

I have created an admin panel to insert new challenges. The solution I was trying was to have the script that added a new challenge both insert the challenge into the challenges table but also to alter the users table and create a column for the challenge named something like Chal_(ID HERE) with a value of 0 for incomplete and 1 for complete.

 

While at first it seemed like a good idea, this means that the number of columns in the users table is dynamic and that makes the registration script almost impossible to negotiate. Of course when a new user registers, the number of columns to insert wont be correct.

 

So I'm not even sure I'm on the right track.... Any help?

[cags]

From what it sounds like to me, no your not on the right track. But the solution is fairly simple. What you have is a many to many relationship. Their are many users and there are many challenges. A challenge can be attempted by many users and a user can attempt many challenges. The solution is to create a 'linking' table. Generally this is done using the two current table names so in your case it would probably be called user_challenges. This table would store all information relative to a users attempt at a challenge. It would include at the very least a user_id and a challenge_id. In your specific case you might also include `grade` or something along those lines.