Correct data structure Laravel

Question

I currently have this structure for example:

In this example, you can see that it would be possible to bind multiple users, to one book or more.

Problem I am expierincing is as follows:

There's only 1 main owner of a book. The users that get bind afterwards need to have certain permissions, like edit, delete etcetera. What would be a correct way of dealing this in this structure I am providing? Would I need to store the permissions in the pivot table user_book, or have a seperate table?

Answer 1

Here is an abstract idea:

The Book table may contain a field for owner as owner_id (or user_id) so in this case you can create a one-to-one relationship for book and it's owner. The method for relationship in Book model might look like:

public function owner()
{
    // Book belongs to one owner (User)
    return $this->belongsTo('App\User', 'owner_id', 'id');
}

So, $book->owner will return the owner/user who owns the book. The owner_id needs to be set when creating a book. The reverse relation would be one-to-many so in User model you need to create the books method for example:

public function books()
{
    // User has many books
    return $this->hasMany('App\Book', 'owner_id', 'id');
}

So, from the User model you can call:

$books = User::find(1)->books; // User::with('books')->find(1);

So far the owner and book relationship has been declared. Now for users with permissions you may need to create many-to-many relationship between User and Book model using a pivot table (your current structure), for example:

users -> book_user <- books

In both models ('User' and 'Book') you need to create method like this:

// In Book model
public function permittedUsers() // $book->permittedUsers
{
    return $this->belongsToMany(
        'App\User',
        'book_user',
        'book_id',
        'user_id'
    )->withPivot('permission');
}

// In User model (books is used for owner)
public function permittedBooks() // $user->permittedBooks
{
    return $this->belongsToMany(
        'App\Book',
        'book_user',
        'user_id',
        'book_id'
    )->withPivot('permission');
}

Your book_user table may look like this:

id | book_id | user_id | permission

Now, when you bind users to books, save the access level in the permission field using the Unix system's permission algorithm (similar but not same). For this, at first, create a config file in config folder using a unique file name, you may use book.php and declare/return the following array:

// config/book.php
return [
    'editable' => 1,
    'deletable' => 2,
    'all' => 3
];

The Algorithm:

if permission value == 1 then user can edit the book
if permission value == 2 then user can delete the book
if permission value == 3 then user can do both (1+2=3)

So, when you bind the users <-> Books by saving book_id & user_id in the book_user pivot table, also save the permission level in permission field. To achieve this, you may use check boxes and set the value of check boxes as permission's value, for example;

<input name="permission[]" type="checkbox" value=1 /> Edit
<input name="permission[]" type="checkbox" value=2 /> Delete

When submitting the form, just check if the permission was set and then sum/addition the values like:

// if(isset('permission'))
if($permission = Request::get('permission')) {
    $permissionLevel = array_sum($permission);
}

// otherwise invalid data, at least 1 permission must be given

Save the pivot data including $permissionLevel in pivot table. So, when you need to check Book's permissions for a user, you may check if the permission field matches some value in the book_permission table. So as a result you can check easily using something like this:

if($user->permittedBooks->first()->pivot->permission == config('book.edit')) {
    // Can edit the book
}

if($user->permittedBooks->first()->pivot->permission == config('book.all')) {
    // Can do everything
}

That's all and it's an idea. There could be other ways. You may also check this article to get another idea, which is about system wise ACL but could be helpfull. You may also check Unix Permissions Calculator.