Database Structure & Relationships

So you this is how I would plan this out

• User model, User has many books
• Book model, Book has many UserBook / Book has many Authors
• Author model, Author has many books
• UserBook, UserBook belongs to User / UserBook belongs to Book

So with the above in mind

• User model needs no foreign key columns
• Book model needs no foreign key columns
• Author model needs no foreign key columns
• UserBook is a model NOT a pivot. it represents a unique set of data which has foreign key to the related User (with user_id) and related Book (with book_id)

Then you need a pivot table for author_book to make the many to many relationship between authors and books, considering that authors can write many books and book can have more than one author.

Just thinking about getting access to the user's books

User.php

public function userbooks()
{
    return $this->hasMany(UserBook::class);
{

UserBook.php

public function user()
{
    return $this->belongsTo(User::class);
}

public function book()
{
    return $this->belongsTo(Book::class);
}

Book.php

public function userbooks()
{
    return $this->hasMany(UserBook::class);
}

public function authors()
{
    return $this->belongsToMany(Author::class);
}

Author.php

public function books()
{
    public function belongsToMany(Book::class);
}

When listing the user's books, I would go indirectly like

$userBooks = $user->userbooks()->with('book')->get();

and then you have access to the customizations in $userbooks and the original via $userbook->book->title;

If you don't want access to the customization you can also use the hasManyThrough relationship;

User.php

public function userbooks()
{
    return $this->hasMany(UserBook::class);
{

public function books()
{
    return $this->hasManyThrough(Book::class , UserBook::class);
}

Now you should be able to do

    $books = $user->books()->get();

Hope this helps.

(I've not considered sources as you have not said too much about that, but my guess is that there would be a model+table for Source and a key on the book model so that it can belong to a source)

@Snapey Yes, this helps. Seeing UserBooks as Model and not a pivot table makes sense. I just assumed that anytime you combine two tables, you are creating a pivot table. Instead, I should think as pivot tables needed when there are many to many relationships, right? What you suggested really does simplify it all.

The rule of thumb I use is if the table only joins two tables then its fine as a pivot. Once it starts to acquire attributes of its own then its better as a model.

As you said that the user would apply their own customisations then I assumed this table would tend to grow in complexity.

@Snapey Your approach is working great. Let's take this a bit further. A user can group his books in collections. So, I want to query for the user's books in a specific user collection. Here is my model and table.

"Collection" model:

• id
• user_id
• name

"book_collection" table (pivot):

• collection_id
• user_book_id

As of now, my eloquent statement is this:

$books = User::find($user_id)->userbooks()->join('books', 'books.id', '=', 'user_books.book_id');

UserBook.php

public function collection()
{
    return $this->belongsToMany(Collection::class);
}

Collection.php

public function books()
{
    return $this->hasMany(UserBook::class);
}

I guess the user has many collections but collections only belong to one user, so no pivot there.

A collection has many UserBook and UserBook can be in many collections, so a pivot is required collection_userbook containing collection_id and userbook_id

@Snapey I believe I'm getting closer. However, I need to drill down into the relationships one more step. The following query successfully return a user's collection of userbooks. However, my "userbooks" is not relating the actual "book" data.

$books = User::find($user_id)
            ->collections()
            ->where('id', '=', $collection_id)
            ->with('userbooks')
            ->get();

Userbook.php

public function collections()
{
    return $this->belongsToMany('App\Collections');
}

Userbook model should have a relation book()

and then load both relations;

$books = User::find($user_id)  
        ->collections()
        ->where('id', '=', $collection_id)
        ->with('userbooks.book')
        ->get();