schema 迁移：PostgreSQL 中的一对多、多对多
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29664019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
schema migration: one-to-many, many-to-many in PostgreSQL
提问by kfmfe04
Background
背景
In MySQL, I need to model one-to-many with two tablesand many-to-many with three tables, if I want to keep normalized tables.
在 MySQL 中，如果我想保留规范化表，我需要对两个表进行一对多建模，对三个表进行多对多建模。
I am investigating a migration to PostgreSQLwhich, amazingly, allows for vectorand even multidimensional vectorfields!
我正在研究迁移到PostgreSQL，令人惊讶的是，它允许向量甚至多维向量字段！
Questions
问题
What are the cannonical mappings for one-to-manyand many-to-manyin PostgreSQL?
有哪些cannonical映射一个一对多和多对一许多在PostgreSQL的？
Is one-to-manysimply one tablewith a vectorfield?
Is there a cannonical way to model many-to-manyor does it depend on the situation (like how I need to query)?
Are there any caveatsfor using the array field?
是一个一对多只是一个表与矢量场？
是否有一种规范的方法可以对多对多进行建模，还是取决于具体情况（例如我需要如何查询）？
使用数组字段有什么注意事项吗？
回答by Craig Ringer
In PostgreSQL you should generally stick to the relational modelling, just like you are currently using in MySQL.
在 PostgreSQL 中，您通常应该坚持关系建模，就像您目前在 MySQL 中使用的那样。
PostgreSQL's arrays are useful, but should not be your first choice for data modelling for numerous reasons:
PostgreSQL 的数组很有用，但由于多种原因，它不应该是您进行数据建模的首选：
- coarse fetch, lock and write granularity for arrays;
- lack of foreign key target support (true in 9.4 at least; 9.5 was possibly adding array FK support, but it was dropped due to performance issues);
- limited support in client libraries and applications
- 数组的粗取、锁定和写入粒度；
- 缺乏外键目标支持（至少在 9.4 中是这样；9.5 可能添加了数组 FK 支持，但由于性能问题而被删除）；
- 客户端库和应用程序支持有限
Notably, when you update an array, you must update the wholearray, rewriting the whole array. In-place updates can't be done because of MVCC.
值得注意的是，当你更新一个数组时，你必须更新整个数组，重写整个数组。由于 MVCC，无法进行就地更新。
Arrays are great when you're building complex queries, and for some denormalizing tasks where you want to create materialised views for performance reasons. They should notbe your first choice for modelling the authoritative data storage.
当您构建复杂的查询时，以及出于性能原因想要创建物化视图的一些非规范化任务，数组非常有用。他们应该不会是建模权威数据存储的首选。
The canonical mappings of one-to-many and many-to-many in PostgreSQL are exactly the same as in any relational database:
PostgreSQL 中一对多和多对多的规范映射与任何关系数据库中的完全相同：
1:m
1：米
CREATE TABLE parent (
parent_id integer primary key,
...
);
CREATE TABLE child (
child_id integer primary key,
parent_id integer not null references parent(parent_id),
...
);
m:n:
男：男：
CREATE TABLE m(
m_id integer primary key,
...
);
CREATE TABLE n(
n_id integer primary key,
...
);
CREATE TABLE m_n (
m_id integer references m(m_id),
n_id integer references n(n_id),
PRIMARY KEY(m_id, n_id),
...
);
