Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Junction tables vs foreign key arrays?

Tags:

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?

like image 373
randomguy Avatar asked Dec 12 '12 12:12

randomguy


People also ask

What is the purpose of Junction table?

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.

Can a foreign key be an array?

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.

Does it matter which table has the foreign key?

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.

What is the primary key in a junction table?

The primary key is the clustered index for the table, which means that it determines the order in which rows are stored on disc.


1 Answers

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.

like image 123
David Aldridge Avatar answered Sep 21 '22 16:09

David Aldridge