My second thought after posting previous message is to modify schema of ScoreBoar and make it a real scoreboard.
Old: ScoreBoard(scoreboard_id as primary key, quizz_id as foreign key to Quizzes, 100, 90, 80, 70,60,50,40,30,20,10,0). New: ScoreBoard( identity id, quizz_id, foreign key to quizzes, user_id, foreign key to User user_score ) Table ScoreBoard has one-to-one to both table User and Quizzes. New schema would allow to post absolute value for each user and also it's easy to calculate percentage of other user's result below or above the current user's with sorting on user_score. right? On Wed, Apr 21, 2010 at 2:38 PM, bobo <[email protected]> wrote: > 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]<google-appengine-java%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-appengine-java?hl=en. > > -- 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.
