SQLite Quiz with Questions Table and Categories Table

Question

Edit: I changed my approach. I am trying to add categories to my existing SQLite quiz. This is my approach, what do you think about it? Do you think the foreign key restriction is necessary? I am a total SQLite noob and it's the first time I use a foreign key.

public class QuizDbHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "MyAwesomeQuiz.db";
private static final int DATABASE_VERSION = 1;

private SQLiteDatabase db;

public QuizDbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    this.db = db;

    final String SQL_CREATE_CATEGORIES_TABLE = "CREATE TABLE " +
            CategoriesTable.TABLE_NAME + "( " +
            CategoriesTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            CategoriesTable.COLUMN_NAME + " TEXT " +
            ")";

    final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
            QuestionsTable.TABLE_NAME + " ( " +
            QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            QuestionsTable.COLUMN_QUESTION + " TEXT, " +
            QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
            QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
            QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
            QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
            QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
            QuestionsTable.COLUMN_CATEGORY + " INTEGER, " +
            "FOREIGN KEY(" + QuestionsTable.COLUMN_CATEGORY + ") REFERENCES " +
            CategoriesTable.TABLE_NAME + "(" + QuestionsTable._ID + ")" +
            ")";

    db.execSQL(SQL_CREATE_CATEGORIES_TABLE);
    db.execSQL(SQL_CREATE_QUESTIONS_TABLE);
    fillCategoriesTable();
    fillQuestionsTable();
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + QuestionsTable.TABLE_NAME);
    onCreate(db);
}

@Override
public void onConfigure(SQLiteDatabase db) {
    super.onConfigure(db);
    db.setForeignKeyConstraintsEnabled(true);
}

private void fillQuestionsTable() {
    Question q1 = new Question("Easy: A is correct",
            "A", "B", "C", 1, Question.DIFFICULTY_EASY, 1);
    addQuestion(q1);
    Question q2 = new Question("Medium: B is correct",
            "A", "B", "C", 2, Question.DIFFICULTY_MEDIUM, 2);
    addQuestion(q2);
    Question q3 = new Question("Medium: C is correct",
            "A", "B", "C", 3, Question.DIFFICULTY_MEDIUM, 3);
    addQuestion(q3);
    Question q4 = new Question("Hard: A is correct",
            "A", "B", "C", 1, Question.DIFFICULTY_HARD, 4);
    addQuestion(q4);
    Question q5 = new Question("Hard: B is correct",
            "A", "B", "C", 2, Question.DIFFICULTY_HARD, 5);
    addQuestion(q5);
    Question q6 = new Question("Hard: C is correct",
            "A", "B", "C", 3, Question.DIFFICULTY_HARD, 6);
    addQuestion(q6);
}

private void addQuestion(Question question) {
    ContentValues cv = new ContentValues();
    cv.put(QuestionsTable.COLUMN_QUESTION, question.getQuestion());
    cv.put(QuestionsTable.COLUMN_OPTION1, question.getOption1());
    cv.put(QuestionsTable.COLUMN_OPTION2, question.getOption2());
    cv.put(QuestionsTable.COLUMN_OPTION3, question.getOption3());
    cv.put(QuestionsTable.COLUMN_ANSWER_NR, question.getAnswerNr());
    cv.put(QuestionsTable.COLUMN_DIFFICULTY, question.getDifficulty());
    cv.put(QuestionsTable.COLUMN_CATEGORY, question.getCategory());
    db.insert(QuestionsTable.TABLE_NAME, null, cv);
}

private void fillCategoriesTable() {
    Category c1 = new Category("Cat 1");
    addCategory(c1);
    Category c2 = new Category("Cat 2");
    addCategory(c2);
    Category c3 = new Category("Cat 3");
    addCategory(c3);
}

private void addCategory(Category category) {
    ContentValues cv = new ContentValues();
    cv.put(CategoriesTable.COLUMN_NAME, category.getName());
    db.insert(CategoriesTable.TABLE_NAME, null, cv);
}


public List<Category> getAllCategories() {
    ArrayList<Category> allCategories = new ArrayList<>();
    db = getReadableDatabase();
    Cursor c = db.rawQuery("SELECT * FROM " + CategoriesTable.TABLE_NAME, null);

    if (c.moveToFirst()) {
        do {
            Category category = new Category();
            category.setId(c.getInt((c.getColumnIndex(CategoriesTable._ID))));
            category.setName(c.getString(c.getColumnIndex(CategoriesTable.COLUMN_NAME)));
            allCategories.add(category);
        } while (c.moveToNext());
    }

    c.close();

    return allCategories;
}

public ArrayList<Question> getAllQuestions() {
    ArrayList<Question> questionList = new ArrayList<>();
    db = getReadableDatabase();
    Cursor c = db.rawQuery("SELECT * FROM " + QuestionsTable.TABLE_NAME, null);

    if (c.moveToFirst()) {
        do {
            Question question = new Question();
            question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
            question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
            question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
            question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
            question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
            question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
            questionList.add(question);
        } while (c.moveToNext());
    }

    c.close();
    return questionList;
}

public ArrayList<Question> getQuestions(String difficulty) {
    ArrayList<Question> questionList = new ArrayList<>();
    db = getReadableDatabase();

    String[] selectionArgs = new String[]{difficulty};
    Cursor c = db.rawQuery("SELECT * FROM " + QuestionsTable.TABLE_NAME +
            " WHERE " + QuestionsTable.COLUMN_DIFFICULTY + " = ?", selectionArgs);

    if (c.moveToFirst()) {
        do {
            Question question = new Question();
            question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
            question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
            question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
            question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
            question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
            question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
            questionList.add(question);
        } while (c.moveToNext());
    }

    c.close();
    return questionList;
}
}

Answer 1

It's always desirable to have int as FK. This way, you could change the name of the category without removing all the relationships (in this case, their questions). You also have to think if it makes sense to have question with a category that no longer exists.

About having or not a table for Category... think if it could be possible to have a Category without questions (for instance, you first create a category, then you add questions to it).

Although it's difficult at the beginning, you should forget about SQLite, MySQL or whatever database engine you use. You must think in your model and what is needed in your business. Having that clear, it'll be easier to you to find the translation to SQLite.

I know I'm making more questions than answering yours, but we need to know exactly what you needed in order to know if what you present us in the code is valid to your or not.