结VS表的外键阵列？ [英] Junction tables vs foreign key arrays?
问题描述
林建模许多一对多关系，其中的关系被访问的大部分时间仅从一侧。它更像是一个层次，被访问自上而下，而不是周围的其他方法。
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.
标准的M2M实施将使用两个结表， surveys_questions 和 questions_answers 。相反，我想使用PostgreSQL的整数数组存储 question_ids 在调查和 answer_ids 的质疑。
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.
我们可以利用ANY运算符来查询外键阵列匹配的所有行。
We can utilize the ANY operator to query all rows matching the foreign key array.
如何，我们将查询所有与他们提出的问题和问题的答案使用SQL调查？
How would we query for all the surveys with their questions and questions's answers using SQL?
我们如何能匹配行的顺序与外键返回数组？ IE浏览器。使用question_ids = [1,2,3]是保证与顺序1，2，3返回问题的行
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)?
您会建议吗？是否有关于这样的造型M2M一些资源？
Would you suggest this? Are there some resources about modeling M2M like this?
更新
有是为阵的外键参照完整性添加到PostgreSQL 9.3的建议，但它并没有得到包括：<一href=\"http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/\">http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/
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有关使用外键阵列<一个维持秩序的问题href=\"http://stackoverflow.com/questions/2486725/postgresql-join-with-array-type-with-array-elements-order-how-to-implement\">PostgreSQL与数组类型与数组元素顺序，如何实现？
SO question about maintaining order using foreign key array PostgreSQL JOIN with array type with array elements order, how to implement?
推荐答案
使用结合表的方法。该阵列的方法是非标准的，以至于你要问它会是多少工作的问题，而另一种是完全标准的。
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.
这篇关于结VS表的外键阵列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
