Hi After leaning JPA tutorial with Getting Started tutorial, I would like to try creating my own web application called on-line quizzes with Google Engine. This application would provide users quizzes with uncertain number of questions for each quizz. And it displays the result a user plays, as well as tells the rank of the result among participants and percentage of performers below the result the user receives. If a user desires, the application would be able to tell the historic records of quizzes the user played in the past. I am stuck on how to design and flat tables for this application. Below is what I tried and feel the design is a bit of awkward and it may not fit into tasks the application would perfom:
1. User table(user_id, first_name, last_name) 2. Quizzes tables(quizz_id, quizz_name) User and Quizzes tables has many-to-many relationship. Each user has a number of quizzes and each quizz can have a number of users 3. Join or associate table User_Quizzes(user_id, quizz_id). Both ids are from User and Quizz table as a foregn key to its source table, and both as a composite primay key for this join table. 4. Record table(record_id, score, qizz_id, user_id). It has one-to- many relationship between User and Record table. Foreign key in Record table is a user_id. So we can retrieve historic records for a particular user; 5. ScoreBoard table, this is where I am confused and not confident. Each quizz will store distribution of users with different level of scores for a particular quizz, let's say 10 as an interval for score level and then it can tell percentage of users played above or below to this user. So it's one-to-one relationship between each quizz and its corresponding scoreboard. ScoreBoard(scoreboard_id as primary key, quizz_id as foreign key to Quizzes, 100, 90, 80, 70,60,50,40,30,20,10,0). If a user plays a quizz with 15 questions and have 11 correct, it's 73% correct for that quizz. I will update the column of 70 with whatever value there increased by 1. At this time, the total percent of peope below this user is: sum( values in column 60, 50, 40, ....,0)/ total number of users participated in the quizz. With this database design above, firstly the application is not able to give the rank of absolute scores with the user's result(but can give a level rank). Should I create another table; Second, I am not sure if the design can eventually fit into DataNucleus JPA implementation. Do you have a better idea or experiences to make this type of database work? My question may look dumb to you but please shed me a light...thanks! Hope this question is not too boring to you. -- You received this message because you are subscribed to the Google Groups "Google App Engine for Java" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/google-appengine-java?hl=en.
