CODEWITHSUNDEEP
google-app-enginegoogle-cloud-datastore
LA_
LA_

Reputation: 20409

How to organize the data in application database?

My application should contain list of questions + user answers. How should I organize the database:

             question1   question2   question2   ...         questionN
user_id_1    yes         no          yes         ...         yes
user_id_2    no          no          yes         ...         no
...
user_id_N    yes         yes         yes         ...         yes

Looks like I need to create separate table with questions and assign id to each question. How another table should look like (since number of columns is not fixed)? Or, should I have 2 more tables?

Later on I will also need to:

  1. calculate how many users answered 'yes' on questionN;
  2. how many friends (another table or json data) of *user_id_N* answered 'yes' on questionN.

Should I query database each for getting these numbers, or should I have separate database and keep counters there each time user answer (looks possible for item 1 only since friends list can be changed anytime).

Upvotes: 0

Views: 414

Answers (3)

Nick Johnson
Nick Johnson

Reputation: 101139

A standard way to do this is to store each answer as a separate entity - conceptually the same as you depict, but without the requirement to modify your structure as you add new questions. Here's an example set of model definitions that achieves this:

class UserInfo(db.Model):
  # Anything you want to store about the user

class Question(db.Model):
  text = db.TextProperty(required=True)
  # Anything else you want to store about the question

class Answer(db.Model):
  user = db.ReferenceProperty(UserInfo, required=True)
  question = db.ReferenceProperty(Question, required=True)
  answer_text = db.TextProperty(required=True)

Upvotes: 2

JAB
JAB

Reputation: 21079

Using a separate table to keep track of the questions themselves might be a good idea. By the way, if you weren't simply leaving out a header for it, your list of user IDs should itself be a specific column in the answer table. It would probably be a good idea to use a separate table to keep track of who is friends with who, though.

Also, while I'm not experienced with accessing a GAE datastore, it's fairly simple to take a count of specific answers in a single column using SQL, at least. SELECT COUNT(questionN) FROM AnswerTable WHERE questionN='yes' would be what you'd use as an SQL query.

Note that if you went with Limey's suggestion for the design, the equivalent SQL query would be more like SELECT COUNT(answer) FROM AnswerTable WHERE questionID='questionN' AND answer='yes'.

Upvotes: 1

Limey
Limey

Reputation: 2772

If you are only storing whatever what answered, you should be able to do that with 3 tables. One for your questions with a one to many to a table with the answers and then another table for the users with a one to many to the answer they provided.

Upvotes: 1

Related Questions