CODEWITHSUNDEEP
iphonedatabasesqlite
Mark
Mark

Reputation: 621

Check my design of database tables for SQLite

I'm making a trivia game and I was wondering if this approach was OK?

trivia_table
    *movie_id
    name
    genre
question_table
    *movie_id
    *question_id
    question
    difficulty
answers_table
    *question_id
    A
    B
    C
    D
    Answer

So each movie will have its own table of questions.

I also thought that maybe I could throw all my questions in one big table and by adding the number of questions there are for each movie in the question_table and then adding the pk movie_id to the answers_table as well?

Upvotes: 0

Views: 109

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753785

You should combine the question and answer tables - you will have one answer row for each question. A single question won't have multiple answer rows. A single answer won't apply to multiple questions.

You should not have separate tables for each movie (or a separate table for each movie as revised).

If the question ID is unique (across all movies), you can make it the primary key of the combined Q&A table.

Your 'trivia_table' would be better named 'movies'. Your combined Q&A table could be called 'trivia'. There is no virtue in the _table suffix on the table name.

Upvotes: 1

Related Questions