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?

In general you shouldn't need to use `array`s simply due to a x-to-many relationship, although `array`s certainly can be used in a variety of ways. Usually, one would simply connect them via *Foreign Key* relationships. Is there a reason why that won't work in your case? — khampson, Apr 16 '15 at 03:07
Demo implementation of a many-to-many model: http://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql/9790225#9790225 — Erwin Brandstetter, Apr 16 '15 at 04:08

score 34 · Accepted Answer · answered Apr 16 '15 at 03:16

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

Could this work if `m_n` contained multiple column references to `m`? So the table definition would be along the lines of `m_id_1 integer references m(m_id), m_id_2 integer references m(m_id) ...` ? — austin_ce, Aug 06 '18 at 16:32
@AustinCawley-Edwards I don't really understand, try posting a new question and adding a link from your new one to this one for people to help understand the context. — Craig Ringer, Aug 20 '18 at 08:57

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

1 Answers1