Vou criar um sistema de exame baseado em múltipla escolha. Sou novo no design de banco de dados e quero sua ajuda para responder algumas perguntas. Como modelar perguntas e categorias de perguntas?
Contexto: Existem vários assuntos (por exemplo: física, química, matemática, etc.). Cada assunto tem seções e cada seção tem capítulos. Por exemplo: Química tem seções: físico-química, química orgânica e química inorgânica. Cada seção tem capítulo relacionado.
E há perguntas e cada pergunta cai em um dos capítulos. Pelo contexto acima, fica claro que cada capítulo pertence a uma seção única de um assunto único. Agora, como devo continuar projetando o banco de dados para isso?
Opção 1:
questions:
question_text varchar,
...
chapter_id int foreign_key
chapters:
chapter_name varchar,
section_id int foreign_key
section:
section_name varchar,
subject_id int foreign_key
subject:
id int primary_key,
subject_name varchar,
Mas acho que isso levará a consultas complexas quando eu quiser encontrar perguntas por assunto e seções. Primeiro preciso encontrar todas as seções e depois todos os capítulos e depois encontrar as questões que pertencem a determinadas questões.
opção 2:
questions:
question_text varchar
subject_id int foreign_key,
section_id int foreign_key,
chapter_id int foreign_key,
subjects:
subject_id int primary_key,
subject_name varchar
sections:
section_id int primary_key,
section_name varchar
chapters:
chapter_id int primary_key,
chapter_name varchar
Neste caso, não haverá relação entre assuntos, seções e capítulos.
Eu adoraria ouvir algumas idéias.
A opção 1 é uma solução normalizada. A solução normalizada é boa para começar. Não acho que a consulta seja complexa, para ocultar a complexidade você pode usar o view.
A opção 2 é desnormalizada. Oferece melhor desempenho de leitura com risco de inconsistência nas atualizações. (como se você mudasse o capítulo para um assunto diferente, não se esqueça de atualizar a tabela de perguntas)