Databases
StackOverflow2013
Postsdbo
POSql table or Mongo document structure design for complex data structrue
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSql table or Mongo document structure design for complex data structrue
    primarykey
    data
    text
    <p>I have a requirement which is build a question survey system. Simply say, it need question, predefine answer and user's answers record.</p> <ul> <li>Question need a question id, question text</li> <li>Answer need a answer id, answer text</li> <li>User's answer record need a record id, user id, question id, answer id,date,os,ip,browser info, is live</li> </ul> <p>For user record, I need to keep all history, that's why I need a "is live" column. So only the latest answer for each user is true. When user answer a same question again, all exist answer record for this user will be history( is live = false ).</p> <p>Seems simple structure. But When I got more than 100,000 question, more than 1 million users, and each user for each question have more than 20 answer record, then the records are more than 100,000 * 1,000,000 * 20 = 2，000,000,000,000 records. Then it become a big issue.</p> <p>I also need to describe how I need to use this data. I need to provide another system, which can use user's record to target a group of users by define a question answer criteria. For example:</p> <ol> <li><code>(Q1=A1 &amp;&amp; Q2=A3 &amp;&amp; Q3=A5 &amp;&amp; (Q4=A8 || Q5=A9))</code> criteria 1</li> <li><code>(Q1!=A1 &amp;&amp; Q2=A3)</code> criteria 2</li> <li><code>(Q4=A8 || Q5!=A9)</code> criteria 3</li> </ol> <p>After I define the criteria:</p> <ol> <li>I need to provide one api to get all user ids who match a criteria (api1)</li> <li>I need to provide one api to get all criterias for a user (api2)</li> </ol> <p>The api need fast and live. And api will be called frequently.</p> <p>So just imagine when there are 200,000,000,000 records in one table. The api call will be very slow or even kill the db.</p> <p>So, I have some solution which is not good, I just list here so we can discuss:</p> <ol> <li>Each question got a single table to save all user record for this question.</li> <li>Each user got a single table to save all question record for this user.</li> <li>Both 1 and 2</li> </ol> <p>But I can see there solution is not very good and efficient. So want to discuss about it here. Doesn't matter what kind of technology (sql, nosql, hadoop etc...)</p> <p>Please put you thoughts here. Thanks</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USZhe
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSql table or Mongo document structure design for complex data structrue
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSql table or Mongo document structure design for complex data structrue
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload