Put other table to another table's column

[alz10]

I'm building a quiz app and luckily i found supabase which is super cool. I've been searching for hours on how to do this but no luck (I'm new to sql/postgresql database).

Basically I have a mock_exams table that handles all set exam and it has set_questions column which i want to have all the data from another table (set_1_questions and set_2_questions) so that when i read all rows in mock_exams table, i can also get the list of questions from these two other tables.

Here's what inside the set_#_questions tables

What i did so far is to add foreign key and read foreign tables using the query below and i got the data from other table but it wont allow me add more questions because it says the id of each questions must be unique, if i increment the id then an error violate foreign key constraint appears.

let { data: mock_exams, error } = await supabase
  .from('mock_exams')
  .select(`
    *,
    set_1_questions (
      *
    )
  `)

If that's impossible, is there like other way to do that? The only solution i can think of for now is to put all list of questions in set_questions column which is a jsonb data type but i think it is best to put it in another table so that i can easily manage it

[kiwicopple]

Hey @alz10

Hopefully I have read your question correctly - I think you don't need to have 2 questions tables - you just need one:

create table questions (
  -- ... your columns
  question text,
  -- ... your columns
  mock_exam references mock_exams -- this is where a question can "belong" to an exam
)

Then your supabase query would be:

let { data: mock_exams, error } = await supabase
  .from('mock_exams')
  .select(`
    *,
    questions (
      *
    )
  `)

Let me know if I misunderstood your schema