Styjun

Advice for making/keeping shredded chicken moist?

Machine Learning Golf: Multiplication

Wouldn't putting an electronic key inside a small Faraday cage render it completely useless?

How do resistors generate different heat if we make the current fixed and changed the voltage and resistance? Notice the flow of charge is constant

Why does "sattsehen" take accusative "mich", not dative "mir"? Even though it is not "me" that I'm looking at?

Taking my Ph.D. advisor out for dinner after graduation

What is the meaning of "prairie-dog" in this sentence?

What is the maximum amount of diamond in one Minecraft game?

Why do we need a bootloader separate from our application program in microcontrollers?

Did William Shakespeare hide things in his writings?

In the Seventh Seal why does Death let the chess game happen?

Did Stalin kill all Soviet officers involved in the Winter War?

Will Jimmy fall off his platform?

What is the shape of the upper boundary of water hitting a screen?

Gory anime with pink haired girl escaping an asylum

Options for quick email reply to the effect of "I've just done it" or "I've taken care of it"

Do grungs have a written language?

How do I check that users don't write down their passwords?

Why was no first prize awarded at a competition?

Bringing coumarin-containing liquor into the USA

As a supervisor, what feedback would you expect from a PhD who quits?

Soda water first stored in refrigerator and then outside

Who goes first? Person disembarking bus or the bicycle?

How predictable is $RANDOM really?

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

Is storing a delimited list in a database column really that bad?Recommended SQL database design for tags or taggingWhat is the most efficient/elegant way to parse a flat table into a tree?What's the difference between identifying and non-identifying relationships?Insert, on duplicate update in PostgreSQL?When should I use cross apply over inner join?Is storing a delimited list in a database column really that bad?What are the options for storing hierarchical data in a relational database?Converting a many-to-many relationship to one-to-many in PostgreSQLAdd more than one data in a field in SQLDelete empty rows in one-to-many relationship (postgresql)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

-1

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?

sql postgresql database-design many-to-many one-to-many

share|improve this question

edited Mar 26 at 0:14

Erwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

asked Mar 25 at 20:29

RTWRTW

1,53811 gold badge55 silver badges2727 bronze badges

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  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 at 21:03
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of Is storing a delimited list in a database column really that bad?
  
  – philipxy
  Mar 26 at 1: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. How to Ask
  
  – philipxy
  Mar 26 at 1:22
  

  
  
  
  

add a comment | 

-1

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?

sql postgresql database-design many-to-many one-to-many

share|improve this question

edited Mar 26 at 0:14

Erwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

asked Mar 25 at 20:29

RTWRTW

1,53811 gold badge55 silver badges2727 bronze badges

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  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 at 21:03
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of Is storing a delimited list in a database column really that bad?
  
  – philipxy
  Mar 26 at 1: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. How to Ask
  
  – philipxy
  Mar 26 at 1:22
  

  
  
  
  

add a comment | 

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?

sql postgresql database-design many-to-many one-to-many

share|improve this question

edited Mar 26 at 0:14

Erwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

asked Mar 25 at 20:29

RTWRTW

1,53811 gold badge55 silver badges2727 bronze badges

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

share|improve this question

edited Mar 26 at 0:14

Erwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

asked Mar 25 at 20:29

RTWRTW

1,53811 gold badge55 silver badges2727 bronze badges

share|improve this question

edited Mar 26 at 0:14

Erwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

asked Mar 25 at 20:29

RTWRTW

1,53811 gold badge55 silver badges2727 bronze badges

edited Mar 26 at 0:14

Erwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

edited Mar 26 at 0:14

Erwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

asked Mar 25 at 20:29

RTWRTW

1,53811 gold badge55 silver badges2727 bronze badges

asked Mar 25 at 20:29

RTWRTW

1,53811 gold badge55 silver badges2727 bronze badges

1 Answer
1

active

oldest

votes

1

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.

share|improve this answer

edited Mar 26 at 0:18

answered Mar 26 at 0:06

Erwin BrandstetterErwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55345937%2fimplement-one-to-many-relationship-with-an-array-of-ids%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

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.

share|improve this answer

edited Mar 26 at 0:18

answered Mar 26 at 0:06

Erwin BrandstetterErwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

add a comment | 

1

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.

share|improve this answer

edited Mar 26 at 0:18

answered Mar 26 at 0:06

Erwin BrandstetterErwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

add a comment | 

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.

share|improve this answer

edited Mar 26 at 0:18

answered Mar 26 at 0:06

Erwin BrandstetterErwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

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

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

share|improve this answer

edited Mar 26 at 0:18

answered Mar 26 at 0:06

Erwin BrandstetterErwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

answered Mar 26 at 0:06

Erwin BrandstetterErwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges

answered Mar 26 at 0:06

Erwin BrandstetterErwin Brandstetter

369k7878 gold badges672672 silver badges849849 bronze badges