CODEWITHSUNDEEP
sqlpostgresqlschema
user623990
user623990

Reputation:

PostgreSQL one-to-many with an unknown amount

I have two tables: response and answer. A single response can contain multiple answers. So response should have a FK to the answer table. However there's no limit on the amount of answers one response can have.

Is it possible to specify a one-to-many relationship between these two tables without having to hardcode a specific number of columns?

Upvotes: 2

Views: 42

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32402

Is it possible to specify a one-to-many relationship between these two tables without having to hardcode a specific number of columns?

You can create a join table consisting of the two foreign keys where each row would represent an association between a response and an answer i.e. if response with id 1 is associated to answers 1 and 2 your table would contain these two rows

response_id, answer_id
1, 1
1, 2

and you could select your response/answer associations using the following query

select * 
from response r
join response_answer ra on ra.response_id = r.id
join answer a on a.id = ra.answer_id

Edit

The answer above assumes you actually want a many-to-many and not one-to-many relationship (i.e. each answer can belong to many responses as opposed to just one) otherwise the simpler solution is to add the response fk to the answer table.

Upvotes: 1

Related Questions