Laravel中用户-角色-店铺-学校表关系建模最优策略

Great question! The problem you're describing is exactly why Laravel's polymorphic relationships exist—they let you build scalable associations without having to modify your database schema every time you add a new entity like a supermarket later on. Here's how to implement this cleanly with Eloquent:

We'll replace your rigid roles_user pivot table with a polymorphic pivot table (I'll name it role_assignments for clarity). This table will handle associations between users, roles, and any type of entity (schools, stores, future supermarkets, etc.) without needing schema changes down the line.

users
- id (primary key)
- name
- email
- password

roles
- id (primary key)
- name (unique, e.g., 'root', 'user', 'admin')

role_assignments
- id (primary key)
- user_id (foreign key to users.id)
- role_id (foreign key to roles.id)
- assignable_id (foreign key to the related entity's id)
- assignable_type (full class name of the related model, e.g., 'App\Models\School', 'App\Models\Store')

schools
- id (primary key)
- name

stores
- id (primary key)
- name

-- Future: Add a supermarkets table WITHOUT touching role_assignments
supermarkets
- id (primary key)
- name

Define the relationships in your models to leverage Laravel's polymorphic features:

RoleAssignment Model #

This model acts as the polymorphic pivot, connecting users, roles, and assignable entities:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\MorphTo;

class RoleAssignment extends Model
{
    protected $fillable = ['user_id', 'role_id', 'assignable_id', 'assignable_type'];

    // Belongs to a single user
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    // Belongs to a single role
    public function role(): BelongsTo
    {
        return $this->belongsTo(Role::class);
    }

    // Morphs to any assignable entity (School, Store, Supermarket, etc.)
    public function assignable(): MorphTo
    {
        return $this->morphTo();
    }
}

User Model #

Link users to their role assignments, with optional eager loading for convenience:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class User extends Model
{
    // ... existing user code

    // A user has many role assignments
    public function roleAssignments(): HasMany
    {
        return $this->hasMany(RoleAssignment::class);
    }

    // Optional: Eager load roles and their associated entities in one call
    public function rolesWithAssignables()
    {
        return $this->roleAssignments()->with(['role', 'assignable']);
    }
}

School/Store/Supermarket Models #

All assignable entities will use the same morphMany relationship pattern. Here's the School model as an example:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\MorphMany;

class School extends Model
{
    // ... existing school code

    // A school has many role assignments
    public function roleAssignments(): MorphMany
    {
        return $this->morphMany(RoleAssignment::class, 'assignable');
    }

    // Optional: Get all users and their roles for this school
    public function usersWithRoles()
    {
        return $this->roleAssignments()->with(['user', 'role']);
    }
}

The Store (and future Supermarket) models will be identical to the School model—just replace the class name.

Creating Role Assignments #

Assign roles to users for specific entities like your example:

// Get the user, role, and school
$user = User::where('email', 'testuser@email.com')->first();
$rootRole = Role::where('name', 'root')->first();
$highSchool = School::where('name', 'HighSchool1')->first();

// Assign root role for HighSchool1
$user->roleAssignments()->create([
    'role_id' => $rootRole->id,
    'assignable_id' => $highSchool->id,
    'assignable_type' => School::class,
]);

// Assign user role for Store1 to the same user
$userRole = Role::where('name', 'user')->first();
$store = Store::where('name', 'Store1')->first();

$user->roleAssignments()->create([
    'role_id' => $userRole->id,
    'assignable_id' => $store->id,
    'assignable_type' => Store::class,
]);

Querying a User's Roles & Associated Entities #

Fetch all roles a user has, along with the entities they're tied to:

$user = User::with('rolesWithAssignables')->find($userId);

foreach ($user->rolesWithAssignables as $assignment) {
    echo "User has role: {$assignment->role->name} for {$assignment->assignable->name}";
    // Example output: "User has role: root for HighSchool1"
}

Querying Users & Roles for a Specific Entity #

Get all users and their roles for a school or store:

$school = School::with('usersWithRoles')->find($schoolId);

foreach ($school->usersWithRoles as $assignment) {
    echo "User: {$assignment->user->email} has role: {$assignment->role->name}";
}

When you need to add a new entity like a Supermarket:

1. Create a supermarkets table (with id, name, and any other fields)
2. Create a Supermarket model with the same roleAssignments() and usersWithRoles() methods as School/Store
3. That's it—you can immediately start assigning roles to users for supermarkets without modifying the role_assignments table at all.

To prevent duplicate roles (e.g., a user can't be assigned the same role twice for the same entity), add a unique index to the role_assignments table:

ALTER TABLE role_assignments
ADD UNIQUE INDEX user_role_assignable_unique (user_id, role_id, assignable_id, assignable_type);

内容的提问来源于stack exchange，提问作者user1174386