Best approach to n-to-m relations between entities

Question

I am trying to build a Drupal 10 site that has a library of books with poems. Each book has a title, ISBN and other data and a list of the poems included in the book. The list shows the title and the page number of each poem. So far I can create a Content type Book with additional fields and a Content type Poem with additional fields. In the Book type I can create an Entity Reference to Poem.

However, a poem can be published in multiple books and in each book it has a different page number.

If the user selects a poem it should see a list of books it appears in with the page number in the respective book.

I have the information in a SQL database, a table for Book, a table for Poem and a table for PoemInBook.

What is the best way to create these relationships in a Drupal site?

Answer 1

How i'd probably do it is:

1. Create a "Book poems" (or some other logical name) paragraph type
2. Add a entity reference field to your Poem content type e.g. field_poem
3. Add a page number field so the user can enter the page number
4. Add this paragraph as an unlimited field on the Book content type

So effectively a user can create a book and then create as many "book poem" paragraph items as they want

Then probably with custom code to display this information when someone views a poem you'd need to:

1. Get all paragraphs referencing the poem in question via "field_poem"
2. Then look at the parent entity ID that's referencing this paragraph.
3. load that parent ID and this is the book