Schema design

[bzz]

Define SQL schema that is required to store

• files
• questions
• answers

[dpordomingo]

That's what @carlosms and I would propose

- Users # They're the actors of the platform
  - ID
  - github_username
  - auth
  - role # [Requesters|Workers]

- FilePairs # They're the questions of the experiments, where user compares two source codes
  - ID
  - name_A # path
  - name_B # path
  - hash_A # MD5, SHA1... of the content
  - hash_B # MD5, SHA1... of the content
  - content_A
  - content_B
  - experiment_ID
  - diff

- Experiments # They describe the experiments where user compares two source codes.
  - ID
  - name
  - description

- Assignment # It stores the user answers
  - user_ID
  - pair_ID
  - experiment_ID
  - answer # [Yes|KindOf|Nope|Skip|Null]
  - duration # seconds

Assumptions:

• All Assignments are created when the Worker starts answering an Experiment, being Null the value of the answer, and 0 the time elapsed,
• If a worker changes its mind, and answer again an Assignment, the answer will be updated, and the new duration will be summed to the old one (or updated too, if requirements changes)

Simplifications:

• We tried to model the DB considering only what we want to build, avoiding more complex possibilities like making the FilePairs a subtype of Question, having same FilePairs in different Experiments so we'd need a crossing ExperimentFilePairs, and so on.

[dpordomingo]

@smacker already validated it irl; @bzz feel free to reopen if it's needed to change something

[carlosms]

I'm reopening this issue to inform you of a small change that I've made after getting real input data. Please take a look, specially @dpordomingo since you are working on the backend code, and then we can close this issue if you agree.

The import tool merged to master already creates the DB with this schema change.

Now the file_pairs table has the following columns:

• id
• blob_id_a
• repository_id_a
• commit_hash_a
• path_a
• content_a
• hash_a
• blob_id_b
• repository_id_b
• commit_hash_b
• path_b
• content_b
• hash_b
• score
• diff
• experiment_id

[dpordomingo]

Great. Thanks @carlosms
I'll add it to #16
Feel free to reopen if there's any news