Help Modeling Data with Many-to-Many

Questions / Help
,
1

Hello folks,

I’m having some trouble modeling my data with many-to-many associations. I recently purchased Programming Ecto and have been going through it, and while it has illuminated some things for me, I still can’t seem to get my application working quite right, and was hoping I could get some help.

What I am trying to do is create an application to help people manage their books. For my data modeling, I have a user, and the user can have many books, as well as many collections of books. Also, books and collections have a many-to-many relationship, as a book could be in many collections, and a collection can have many books. My schemas so far look something like this.

User Schema

schema "users" do
    field :name, :string
    field :username, :string
    has_one :credential, Credential
    has_many :books, Book
    has_many :collections, Collection

    timestamps()
end

Book Schema

schema "books" do
    field :author, :string
    field :title, :string
    field :isbn, :string
    field :thumbnail, :string
    belongs_to :user, User
    many_to_many :collections, Collection, join_through: CollectionsBooks

    timestamps()
end

Collections Schema

schema "collections" do
    field :name, :string
    belongs_to :user, User
    many_to_many :books, Book, join_through: CollectionsBooks

    timestamps()
end

CollectionsBooks Schema

schema "collections_books" do
    belongs_to :books, Book
    belongs_to :collections, Collection

    timestamps()
end

I’m trying to hook this all up through Phoenix, and I’ve just been having a rough time getting everything to wire up properly. Do you folks have any advice on how to structure some basic CRUD operations with the above model, or how I can improve the model in general?

2

Not sure how you need to model your data but I’ll take a crack at it. In the book Programming Ecto, on page 53 it gives you a simple example of how to do many-to-many relationships.

I think you only need 3 Schemas like the example in the book. I think your schemas should be users, collections and books with collections as the join table something like this.

User Schema

schema "users" do
    field :name, :string
    field :username, :string
    has_one :credential, Credential
    many_to_many(:books, Book, join_through: "collections")

    timestamps()
end

Collection Schema

schema "collections" do
    field :name, :string
    belongs_to :users, User
    belongs_to :books, Book

    timestamps()
end

Book Schema

schema "books" do
    field :author, :string
    field :title, :string
    field :isbn, :string
    field :thumbnail, :string
    many_to_many :users, User, join_through: "collections"

    timestamps()
end

Now you should have a two way many-to-many relationship set up where users can have many books through collections and if needed books can have many users through collections. Hope I didn’t simplify the data model too much.

1 Like
3

That makes a lot of sense. My rationale was that a book might not necessarily be in a collection, so I tried to give the user access to the books directly. However, I suppose I could just make a default collection that a book is automatically added to when created, and that might simplify this whole thing. Then, I’d also be able to follow the example in Ecto more closely to ease my pains. Thanks for the input. It’s very much appreciated.

4

You could make a default collection but I think that would unnecessarily clutter up your database. All you should need to do is write a query to get all books for each user that would return a result set without the collection :name column.

Then write another query when you need to show the books in collections. At this point I think you just need to write the correct queries to get the data you need.

You should also make sure the collection :name column allows null values. So a book can be added without belonging to a collection.

1 Like
5

Ahh, that would definitely be much cleaner. Like I said, new to Ecto and backend development in general, so I’m still trying to wrap my mind around a few things. You’ve set me up for a weekend of success. Thanks again!

1 Like