Table Structure Decisions

Achievement and User Achievemnt table

Achievemnets Table

We have estalished this table to manage various user achievement types, for example, badges, awards, and certificates. Currently, the only available achievement in the table is a badge. This table design allows easy addition of new achievement types by using a separate, abstracted table. This approach allows users to have multiple achievements of different types.

Achievements schema

  create_table "achievements", force: :cascade do |t|
    t.string "name"
    t.string "achievement_type"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

User Achievement Table

This table’s purpose is to store the achieved milestones of users. A user can acquire multiple achievements.

User Achievements schema

 create_table "user_achievements", primary_key: ["user_id", "achievement_id"], force: :cascade do |t|
    t.uuid "user_id", null: false
    t.bigint "achievement_id", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["achievement_id"], name: "index_user_achievements_on_achievement_id"
    t.index ["user_id"], name: "index_user_achievements_on_user_id"
  end

Correct_answer_streak

We have updated the user table by adding a correct_answer_streak column. This column tracks the consecutive correct answers by a user. The streak resets to 0 if a user gets a question wrong or if the user retries a program or episode. With the correct_answer_streak column, we can pick the appropriate badge to award the user.