I am creating a database for an app that helps learn a language. I need to create tests that vary by section (test for grammar, about text, etc), topic and level.
USER
id (pk, autoincrement)
name
last_name
email (unique)
level_id
password
LEVEL
id (pk)
description
SECTION_TYPE
id (pk, no autoinc [to avoid creating a field for level_num])
section_name
TOPIC
?id (pk)
section_id (fk)
level_id (fk)
name
TEST
?id (pk)
topic_id (fk)
num
QUESTION
?id (pk)
test_id (fk)
num
text
has_multi_ans
ANSWER
?id (pk)
question_id (fk)
num
text
is_correct
I have read that using a composite index can facilitate fast search. And searching test by level and topic will be a frequent thing. But in my case a composite index is a unique combination.
I could make it a composite primary key with fields highlighted with tabs instead of creating single primary key id. Because in the first case I will have to also create composite foreign key. That would almost eliminate the need to create table test, but would be messy.
How should I go about this?
Postgres FK referencing composite PK is a bit different since there are many other dependent fields so a unique single primary key is justified.
In the case of topic, the case is simple, provided that the combination of section_id and level_id is to be unique. You can use that combination as your primary key, and you don't need a separate artificial primary key. That will save space and improve performance, because it avoids an additional column and index.
The case of question and test is less clear, because your proposed primary key includes an unspecified column num. I suppose it is some kind of a counter. Sure, if you store that counter in the table and if it is unique per foreign key value, your proposed primary key makes sense. If you generate num with a sequence, great. If that is supposed to be something else, like a "gap-less counter", I recommend that you reconsider the design carefully.