Foreign key referencing unique composite key or single primary key

Question

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.

Please: Paraphrase or quote from other text with credit. Give just what you need & relate it to your problem. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) [mre] [ask] [Help] — philipxy, Mar 22 '23 at 06:29

score 2 · Accepted Answer · answered Mar 22 '23 at 07:11

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.

Foreign key referencing unique composite key or single primary key

1 Answers1