Implement one-to-many relationship with an array of ids?

Question

Is it a good idea to store like 0-1000 of chapters ids in array in Book table and select them from chapters table like this:

SELECT id, name, updated FROM chapters
WHERE id IN (SELECT unnest(chapters_array) FROM books WHERE id=$1);

Or it's better to make separate table and store there chapter_id to book_id relations?

It is a bad idea as it breaks first normal form of relational databases. Make a chapter table with a foreign key to the book table's primary key. You don't want to store multiple data points within one field if you can help it. — J Spratt, Mar 25 '19 at 21:03
Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — philipxy, Mar 26 '19 at 01:20
This is (obviously) a(n easily found) faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. [ask] — philipxy, Mar 26 '19 at 01:22

Erwin Brandstetter · Accepted Answer · 2019-03-26T00:18:51.020

No. Besides breaking normal form and introducing a host of problems, it also seems uncalled for in your case. A book has n chapters. And a chapter always belongs to a single book. That's a plain 1:n relationship like you mentioned in title yourself! You don't need a "separate table". All you need is the ID of the book in each related row of the chapters table. Like:

CREATE TABLE chapters
  id serial PRIMARY KEY
, book_id int REFERENCES books(id) -- !
, name text NOT NULL
, updated timestamptz
);

I added a FOREIGN KEY constraint with short syntax. Details in the manual here. (That's also something you couldn't use with an array.)

Then your query to get all chapters of a given book can simply be:

SELECT id, name, updated
FROM   chapters
WHERE  book_id = $1;

While you don't want anything from the books table, you don't even have to include it in the query. The FK constraints enforces referential integrity anyway.

Implement one-to-many relationship with an array of ids?

1 Answers1