如何在Laravel MySQL中实现数据库特化父子表结构

数据库层实现 #

推荐采用共享主键的特化结构，无冗余关联字段，查询性能高，通过外键保证数据一致性，不会产生孤儿记录。

• 父表sponsors：存储个人、机构两类赞助商的公共属性，新增sponsor_type字段标记所属子类型，值为person/institution
• 子表sponsor_people：存储个人赞助商专属属性，主键同时作为外键关联sponsors.id，设置级联删除
• 子表sponsor_institutions：存储机构赞助商专属属性，主键规则同个人子表

对应Migration代码示例：

// 父表sponsors
Schema::create('sponsors', function (Blueprint $table) {
    $table->id();
    $table->string('sponsor_type'); // 子表类型标记
    // 以下为公共字段，可按业务调整
    $table->string('name');
    $table->string('contact_phone')->nullable();
    $table->text('remark')->nullable();
    $table->timestamps();
});

// 个人赞助商子表sponsor_people
Schema::create('sponsor_people', function (Blueprint $table) {
    $table->unsignedBigInteger('sponsor_id')->primary();
    $table->foreign('sponsor_id')->references('id')->on('sponsors')->onDelete('cascade');
    // 以下为个人专属字段，可按业务调整
    $table->string('id_card_number');
    $table->date('birthday')->nullable();
    $table->string('personal_address')->nullable();
    $table->timestamps();
});

// 机构赞助商子表sponsor_institutions
Schema::create('sponsor_institutions', function (Blueprint $table) {
    $table->unsignedBigInteger('sponsor_id')->primary();
    $table->foreign('sponsor_id')->references('id')->on('sponsors')->onDelete('cascade');
    // 以下为机构专属字段，可按业务调整
    $table->string('credit_code');
    $table->string('legal_person_name');
    $table->string('business_address')->nullable();
    $table->date('establish_date')->nullable();
    $table->timestamps();
});

模型层实现 #

不需要依赖第三方扩展包，通过Laravel原生能力即可实现自动映射、关联写入：

1. 父模型Sponsor，实现子模型自动映射、子表关联定义

// app/Models/Sponsor.php
namespace App\Models;

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

class Sponsor extends Model
{
    protected $fillable = ['name', 'contact_phone', 'remark', 'sponsor_type'];

    // 关联个人子表
    public function personDetail(): HasOne
    {
        return $this->hasOne(SponsorPerson::class, 'sponsor_id');
    }

    // 关联机构子表
    public function institutionDetail(): HasOne
    {
        return $this->hasOne(SponsorInstitution::class, 'sponsor_id');
    }

    // 查询结果自动映射为对应子模型实例
    public function newFromBuilder($attributes = [], $connection = null)
    {
        $model = match ($attributes->sponsor_type ?? null) {
            'person' => new SponsorPerson(),
            'institution' => new SponsorInstitution(),
            default => new static(),
        };
        $model->exists = true;
        $model->setRawAttributes((array)$attributes, true);
        $model->setConnection($connection ?: $this->getConnectionName());
        return $model;
    }
}

2. 个人子模型SponsorPerson，继承父模型，自动处理关联写入、全局查询关联

// app/Models/SponsorPerson.php
namespace App\Models;

class SponsorPerson extends Sponsor
{
    protected $table = 'sponsor_people';
    public $incrementing = false; // 关闭自增主键，使用父表ID

    // 自动填充类型标记
    protected $attributes = [
        'sponsor_type' => 'person'
    ];

    protected $fillable = ['id_card_number', 'birthday', 'personal_address'];

    protected static function booted()
    {
        // 查询时自动关联父表公共字段
        static::addGlobalScope('person_join', function ($query) {
            return $query->join('sponsors', 'sponsors.id', '=', 'sponsor_people.sponsor_id')
                ->select('sponsors.*', 'sponsor_people.*');
        });

        // 创建时自动写入父表、子表
        static::creating(function ($model) {
            $parent = Sponsor::query()->create([
                'sponsor_type' => 'person',
                'name' => $model->name,
                'contact_phone' => $model->contact_phone,
                'remark' => $model->remark
            ]);
            $model->sponsor_id = $parent->id;
            $model->id = $parent->id;
        });
    }

    // 保存时自动同步更新父表公共字段
    public function save(array $options = [])
    {
        Sponsor::query()->where('id', $this->sponsor_id)->update(
            $this->only(['name', 'contact_phone', 'remark'])
        );
        return parent::save($options);
    }
}

3. 机构子模型SponsorInstitution，逻辑同个人子模型

// app/Models/SponsorInstitution.php
namespace App\Models;

class SponsorInstitution extends Sponsor
{
    protected $table = 'sponsor_institutions';
    public $incrementing = false;

    protected $attributes = [
        'sponsor_type' => 'institution'
    ];

    protected $fillable = ['credit_code', 'legal_person_name', 'business_address', 'establish_date'];

    protected static function booted()
    {
        static::addGlobalScope('institution_join', function ($query) {
            return $query->join('sponsors', 'sponsors.id', '=', 'sponsor_institutions.sponsor_id')
                ->select('sponsors.*', 'sponsor_institutions.*');
        });

        static::creating(function ($model) {
            $parent = Sponsor::query()->create([
                'sponsor_type' => 'institution',
                'name' => $model->name,
                'contact_phone' => $model->contact_phone,
                'remark' => $model->remark
            ]);
            $model->sponsor_id = $parent->id;
            $model->id = $parent->id;
        });
    }

    public function save(array $options = [])
    {
        Sponsor::query()->where('id', $this->sponsor_id)->update(
            $this->only(['name', 'contact_phone', 'remark'])
        );
        return parent::save($options);
    }
}

常用操作示例 #

• 新增个人赞助商：直接调用SponsorPerson::create(['name' => '张三', 'contact_phone' => '13xxxxxxxxx', 'id_card_number' => 'xxxxxx'])，系统会自动写入父表和子表
• 新增机构赞助商：调用SponsorInstitution::create([...])，逻辑同上
• 查询所有赞助商：Sponsor::get()，返回结果会自动根据类型映射为SponsorPerson/SponsorInstitution实例，可直接读取对应专属字段，无需手动判断类型
• 单独查询某类赞助商：直接调用SponsorPerson::get()/SponsorInstitution::get()即可，结果已自动关联公共字段
• 删除记录：直接调用模型delete()方法，外键级联会自动删除父表+对应子表记录，无冗余垃圾数据

注意：父表和子表除关联键、时间字段外不要设置同名字段，避免字段冲突。如果业务中赞助商需要和其他模型（比如赞助项目、合同）做关联，关联关系直接定义在父Sponsor模型上即可，两类子模型会自动继承关联能力。

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