Design database

[Limpan]

There needs to be a proper database to manage

• User accounts
• Problems

[Greenheart]

A Problem could consist of:

• Challenge code (string that preserve formatting)
• Challenge name (string)
• Difficulty (1-5)

[Limpan]

I'd suggest that we make the puzzle code JSON encoded for performance reasons. It can then be decoded either by the web server or in the users browser. I also think we need a name and an instruction for the user. Difficulty should be flexible so that it can be changed in the future. Maybe 1-100? 1-5 can then be mapped to 20, 40, 60, 80 and 100. We need a bit of flexibility for #7.

[Greenheart]

JSON encoding would work.

instruction for the user

Absolutely!

1-100 sounds like a good idea. It can be displayed in many different ways. Will make difficulties flexible if they need to change in the future.

[Greenheart]

The User depends on #13

[Greenheart]

Draft for initial DB model

User

id

• integer

username

• string

email

• string

password_hash

• probably store somewhere else?
• maybe use passwordless auth with single signon-email links?

role_id

• reference to an User Role

• Alternative approaches:

  • either a similar solution to chronos (it worked well).
  • Or standard User Roles
  • Could be as simple as an enum value where 1 is highest role and N is lowest.
  • Probably sufficient since we don't have too complicated user roles
    • Normal user, Moderator (and possibly super user. But probably not).

completed_puzzles

• list of Rewards

total_points

• total amount of points

---

Reward

id

• integer

puzzle_id

• The id of the puzzle the user completed

amount

• integer
• how many points the user gained
• This could be calcualted based on puzzle.difficulty - (num_user_attempts * __some_scaling_factor__)

completion_date

• Date or date string --> (year-month-day)
• The date when the puzzle was completed

---

Puzzle / Challenge / Problem

Whatever this should be called.

id

• integer

description

• string
• parsed from the puzzle seed code's docstring
  • Ex: ("""description""")

hints

• List of Hints
• Always on their own line, just above the line they give a hint for
• Parsed from the line comments in the puzzle seed
  • Ex: (# hint)

difficulty

• integer (range 1-100)
• Used to calculate what maximum score a user can gain by completing the puzzle.

---

Hint

id

• integer

puzzle_id

• integer/string

line_number

• integer
• Where the hint should be displayed

messsage

• string (should fit on one line)

---

User Role

permission

• integer (1-N) where 1 is highest permission and N is lowest.
• Using a simple integer gives some benefits:
  • Clear hierarchy.
  • Easy role checks. (if user.role.permission == UserRole.ADMIN (the constant value 1))

display_name

• string

---

Example roles for the User Role table:

Each role's available actions builds on top of each other. Admins can do everything.

User

• Can...
  • submit solutions to puzzles and gain points
• permission: 3
• display_name: 'User'

Moderator

• Can...
  • edit existing puzzles.
  • review new puzzles submitted and either accept, edit or reject them.
• permission: 2
• display_name: 'Moderator'

Administrator

• Can...
  • Manage user's with moderator permissions.
• permission: 1
• display_name: 'Administrator'

[Limpan]

Quick thoughts. It's no biggie to build a proper role system like Flasky and Chronos. It'll be flexible. I think this one will be worth it.

I'll read up on the OAuth solution that the LTI standard and It's learning uses. Maybe we're good using email as identifier but I'd like to make sure.

[Limpan]

Progress has been made. https://github.com/Limpan/parsons-puzzle/tree/feature/database

[Greenheart]

I didn't get any notification for the last message, I'll check test this locally! :+1:

[Greenheart]

@Limpan What needs to be done on this branch?