连接表与外键数组有何区别？

Question

我正在建模多对多关系，其中大部分时间从一侧访问关系。它更像是一个层次结构，从上到下访问而不是反过来。

Survey有并且属于许多Questions，Questions有并且属于许多Answers。

两个关系都必须是多对多的，因为同一个问题可以在不同的调查中重复使用，同样的答案可以在多个问题中使用。这是一个要求。

标准的M2M实现将使用两个连接表，surveys_questions和questions_answers。相反，我正在考虑使用PostgreSQL的整数数组存储Survey中的question_ids和Question中的answer_ids。

我们可以利用ANY运算符查询与外键数组匹配的所有行。

如何使用SQL查询所有具有其问题和问题答案的调查？

如何将返回的行的顺序与外键数组匹配？ 即使用question_ids = [1,2,3]可以保证返回顺序为1、2、3的问题行。

与连接表相比，性能如何（假设有适当的索引，无论它们是什么）？

您会建议这样做吗？ 有关于如何建模多对多关系的资源吗？

更新

PostgreSQL 9.3曾提出添加数组外键的引用完整性，但未被包含：http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/

有一个关于使用外键数组维护顺序的SO问题：PostgreSQL JOIN with array type with array elements order, how to implement?

- randomguy

---

你说的是多对多，但听起来更像是一对多；多对多意味着每个调查与多个问题相关联，每个问题与多个调查相关联，但这听起来有点奇怪。当然，你所说的“has-many”通常是一对多的同义词（多对多通常称为“has-and-belongs-to-many”）。 - SingleNegationElimination

@TokenMacGuy：抱歉造成困惑。问题可以在调查中重复使用，答案也可以跨问题重复使用，这使得它们之间的关系是多对多的。我将用HABTM替换has many关系。 - randomguy

Answer 1

使用连接表方法。数组方法非标准化程度足以使您对它的可行性产生疑问，而另一种方法则完全符合标准。

- David Aldridge

---

连接表相比数组可能会慢很多。请参见 https://gist.github.com/joevandyk/031cf5812bd656887623 - Joe Van Dyk

有时使用数组方法可以提高性能。当然，这是否适用于所有情况还存在疑问，其中一个主要问题是添加/删除新链接需要修改可能很长的行（例如优惠券），而不是插入/删除单个行，并且需要锁定优惠券表。 - David Aldridge

同意！如果您不想在每次插入时修改优惠券表，可以使用coupons_products_array表（coupon_id，product_ids []）代替。但这可能有点傻瓜。 - Joe Van Dyk

@JoeVanDyk 加入操作速度较慢，最好使用查询语句。在我的测试中，使用数组和查询语句没有性能差异，两者都为0.075毫秒。不过，数组的计划时间仍然更快，分别为0.211毫秒和0.769毫秒。 https://gist.github.com/joevandyk/031cf5812bd656887623#gistcomment-1818413 - Michael Buen

维护数组对于高容量表来说听起来像是一个锁定的噩梦。 - Alkanshel

@Amalgovinus 是的，这绝对是一个保证串行化的重点。 - David Aldridge