2
\$\begingroup\$

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;
}
}
\$\endgroup\$
  • \$\begingroup\$ Why are you using SQLite at all, if the questions and answers are already hard-coded in the code? \$\endgroup\$ – 200_success Apr 8 '18 at 20:25
  • \$\begingroup\$ Those are the initial questions and answer. Later I want to add the option to add more within the app. \$\endgroup\$ – Florian Walther Apr 8 '18 at 20:26
2
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Thank you, I actually had the same train of thought. I thought categories should be addable before adding a question of that category. This is why I turned from just having a category column to creating a category table. And questions should not have categories that don't exist, so as far as I understand, this is enforced with foreign keys. \$\endgroup\$ – Florian Walther Apr 9 '18 at 20:17
  • 1
    \$\begingroup\$ Btw, your answer solved a lot of my questions, so it was very helpful! \$\endgroup\$ – Florian Walther Apr 11 '18 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.