Check out some of our datastore docs here: http://code.google.com/appengine/docs/java/datastore/
<http://code.google.com/appengine/docs/java/datastore/>It is extremely important that you understand that App Engine's datastore is non-relational - you don't have "tables", "foreign keys" or "joins" in the sense that you would working with a SQL database, and this is definitely something to keep in mind before you design how your entities (not tables) are related. There's no need to normalize your "schema" - in fact, in many cases, this is worse for you since you don't have foreign key constraints. There are also limitations with regards to complex queries that you should understand. There are no such things as "composite primary keys", though you can simulate the same thing with key names. App Engine's datastore is a distributed key-value store built on top of BigTable, and you should be aware of differences when creating your entities. You'll have a much easier time working with persistence when you're un-tethered from traditional RDBMS schema design. That being said, one thing you can and probably should do is keep a UserTakenQuizzes entity and use a List property to store all the quizzes a user has taken, then filter in memory or on whatever criteria makes sense. It doesn't make sense to store an individual "Join" entity in your case (RecordTable), as this will result in both an index being created as well as the join entity. The same thing can be accomplished with a List. In addition - if the scoreboard has a one-to-one relationship with the Quiz table - why not embed the Scoreboard inside the Quiz? That is, keep the information in the same entity? I'm sure other members of the community will have additional tips to add ... On Wed, Apr 21, 2010 at 12:55 PM, bosun <[email protected]> wrote: > 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]<google-appengine-java%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/google-appengine-java?hl=en. > -- Ikai Lan Developer Relations, Google App Engine Twitter: http://twitter.com/ikai Delicious: http://delicious.com/ikailan ---------------- Google App Engine links: Blog: http://googleappengine.blogspot.com Twitter: http://twitter.com/app_engine Reddit: http://www.reddit.com/r/appengine -- 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.
