I'm modeling many-to-many relationship where the relationship is accessed most of the time from one side only. It's more like a hierarchy, that is accessed top-down and not the other way around.
Survey has and belongs to many Questions has and belongs to many Answers.
Both relationships must be many-to-many because a same question can be re-used across different surveys and same answer in many questions. This is a requirement.
The standard M2M implementation would use two junction tables, surveys_questions
and questions_answers
. Instead, I'm thinking about using PostgreSQL's integer arrays to store question_ids
in Survey and answer_ids
in Question.
We can utilize the ANY operator to query all rows matching the foreign key array.
How would we query for all the surveys with their questions and questions's answers using SQL?
How can we match the order of the rows returned with the foreign key array? ie. using question_ids = [1,2,3] is guaranteed to return question rows with the order 1, 2, 3.
How does this perform performance wise compared to junction tables (assuming proper indexes, whatever they might be)?
Would you suggest this? Are there some resources about modeling M2M like this?
Update
There was a proposal to add referential integrity for array foreign keys to PostgreSQL 9.3, but it didn't get included: http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/
SO question about maintaining order using foreign key array PostgreSQL JOIN with array type with array elements order, how to implement?
A junction table contains the primary key columns of the two tables you want to relate. You then create a relationship from the primary key columns of each of those two tables to the matching columns in the junction table. In the pubs database, the titleauthor table is a junction table.
Not possible, but might be in the future. Foreign Key Arrays is a work in progress in PostgreSQL. Alternatively, you can do the usual approach of creating a junction(or join) table for this. Check this SO question.
A foreign key is simply a field in one table that refers to a key field of another table. It's not absolutely critical to identify the foreign key field as such.
The primary key is the clustered index for the table, which means that it determines the order in which rows are stored on disc.
Use the junction table approach. The array method is non-standard enough that you have to ask questions about how much it would work, whereas the other is completely standard.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With