CODEWITHSUNDEEP
sqlpostgresqldatabase-designmany-to-manyone-to-many
ZiiMakc
ZiiMakc

Reputation: 36876

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

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?

Upvotes: 1

Views: 4102

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

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.

Upvotes: 3

Related Questions