如何保存用户的通知设置laravel5和mysql [关闭]

Question

I am building Restful Api for a mobile application. I need to push the notification to user if he has saved the notification setting to true in his profile.

I have created the following structure:

Users Table:

<kbd>id</kbd> <kbd>name</kbd> <kbd>email</kbd>

<kbd>1</kbd> <kbd>Test</kbd> <kbd>[e-mail]</kbd>

<kbd>2</kbd> <kbd>Test1</kbd> <kbd>[e-mail]</kbd>

Notifications Table:

<kbd>id</kbd> <kbd>notification_name</kbd>

<kbd>1</kbd> <kbd>Like Notification</kbd>

<kbd>2</kbd> <kbd>Comment Notification</kbd>

user_notification Table:

<kbd>id</kbd> <kbd>notification_id</kbd> <kbd>user_id</kbd> <kbd>value (0=off, 1=on)</kbd>

<kbd>1</kbd> <kbd>           1           </kbd> <kbd>     1     </kbd> <kbd>   0   </kbd>

<kbd>2</kbd> <kbd>           2           </kbd> <kbd>     1     </kbd> <kbd>   1   </kbd>

<kbd>3</kbd> <kbd>           1           </kbd> <kbd>     1     </kbd> <kbd>   1   </kbd>

<kbd>4</kbd> <kbd>           2           </kbd> <kbd>     1     </kbd> <kbd>   1   </kbd>

In the above table structure I have created a pivot table with an extra field value for saving settings of a user.

Is there any another great way to do this, in which I can minimize my queries and respond fast

Answer 1

Your pivot table should only contain the two references defined, notification_id and user_id. Adding more fields to that negates it as a pivot table.

You shpuld put the value field on your notifications table instead, perhaps naming it better like is_active. It is your notification that has a setting, right? So it makes sense that the setting goes on the notification, not the pivot table which only serves to connect the two tables.

Also, as a sugestion, have your pivot table alphabetical as per normal convention in Laravel (see the docs)

In your User.php model, add the relationship

public function notifications() {
    return $this->hasMany(App\Notification::class, 'notification_user');
}

When querying this relationship, all you have to do is:

foreach($user->notifications as $notification) {
    if($notification->is_active) {
        // do something
    }
}