schema migration: one-to-many, many-to-many in PostgreSQL

Question

Background

In MySQL, I need to model one-to-many with two tables and many-to-many with three tables, if I want to keep normalized tables.

I am investigating a migration to PostgreSQL which, amazingly, allows for vector and even multidimensional vector fields!

Questions

What are the cannonical mappings for one-to-many and many-to-many in PostgreSQL?

• Is one-to-many simply one table with a vector field?

• Is there a cannonical way to model many-to-many or does it depend on the situation (like how I need to query)?

• Are there any caveats for using the array field?

Answer 1

In PostgreSQL you should generally stick to the relational modelling, just like you are currently using in MySQL.

PostgreSQL's arrays are useful, but should not be your first choice for data modelling for numerous reasons:

• 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

Notably, when you update an array, you must update the whole array, rewriting the whole array. In-place updates can't be done because of 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 not be 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:

1:m

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),
   ...
);