Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

jrdavidson's avatar

Model Ownership

I'm still struggling with the concept of model ownership. What I mean by that is coming up with a way to show who created a particular row in the database through my application. Lets say I have 3 administrators and one of them creates a new user which is only a basic user permissions for the application and I want to show which admin registered that user. I have what I think is a correct migration for something like this but if someone could help me understand if there's a better way to go about this I would certain consider it.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('user_accounts', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('first_name');
            $table->string('last_name');
            $table->string('username')->unique();
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->string('avatar')->unique()->nullable();
            $table->integer('status_id')->unsigned()->default(1);
            $table->integer('role_id')->unsigned();
            $table->integer('creator_id')->unsigned();
            $table->rememberToken();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('user_accounts_profiles', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('user_id')->unsigned()->index();
            $table->string('biography')->nullable();
            $table->string('address')->nullable();
            $table->string('city')->nullable();
            $table->string('state')->nullable();
            $table->integer('postcode')->nullable();
            $table->string('country')->nullable();
            $table->string('phone')->nullable();
            $table->timestamp('birthday')->nullable();
            $table->string('facebook_username')->unique()->nullable();
            $table->string('twitter_username')->unique()->nullable();
            $table->string('google_plus_username')->unique()->nullable();
            $table->string('behance_username')->unique()->nullable();
            $table->string('pinterest_username')->unique()->nullable();
            $table->string('linkedin_username')->unique()->nullable();
            $table->string('github_username')->unique()->nullable();
            $table->string('youtube_username')->unique()->nullable();
            $table->string('instagram_username')->unique()->nullable();
            $table->string('external_link')->unique()->nullable();
            $table->integer('creator_id')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('user_accounts_statuses', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->unique();
            $table->string('label')->unique();
            $table->integer('creator_id')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('user_accounts');
        Schema::drop('user_accounts_profiles');
        Schema::drop('user_accounts_statuses');
    }
}
<?php

use App\UserAccountStatus;
use Illuminate\Database\Seeder;

class UserAccountStatusesTableSeeder extends Seeder
{
    public function run()
    {
        factory(UserAccountStatus::class)->create(['name'  => 'Unconfirmed', 'label' => 'unconfirmed']);
        factory(UserAccountStatus::class)->create(['name'  => 'Active', 'label' => 'active']);
        factory(UserAccountStatus::class)->create(['name'  => 'Inactive', 'label' => 'inactive']);
        factory(UserAccountStatus::class)->create(['name'  => 'Suspended', 'label' => 'suspended']);
        factory(UserAccountStatus::class)->create(['name'  => 'Banned', 'label' => 'banned']);
    }
}
<?php

use App\UserAccount;
use App\Role;
use App\UserAccountStatus;
use Illuminate\Database\Seeder;

class UserAccountsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $myUser = factory(UserAccount::class)
            ->create([
                'first_name' => 'John',
                'last_name' => 'Doe',
                'username' => 'jdoe',
                'email' => 'john@example.com',
                'password' => 'testpass123',
                'avatar' => 'johndoe.jpg',
                'status_id' =>  1,
                'role_id' => 5,
                'creator_id' => 1
            ]);

        $myUser->profile()->create([ 'user_id' => 1 ]);

        factory(UserAccount::class, 10)->create()->each(function($u) {
            $u->profile()->save(factory('App\UserAccountProfile')->make());
        });
    }
}
<?php

use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Model;

class DatabaseSeeder extends Seeder
{
    private $tables = [
        'user_accounts',
        'user_accounts_profiles',
        'user_accounts_statuses',
    ];

    private $seeders = [
        'UserAccountsTableSeeder',
        'UserAccountStatusesTableSeeder'
    ];

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $this->cleanDatabase();

        Model::unguard();

        foreach($this->seeders as $seeder)
        {
            $this->call($seeder);
        }

        Model::reguard();
    }

    private function cleanDatabase()
    {
        DB::statement('SET FOREIGN_KEY_CHECKS=0');

        foreach ($this->tables as $tableName)
        {
            $this->command->info('Deleting existing ' . $tableName . ' data ...');
            DB::table($tableName)->truncate();
        }

        DB::statement('SET FOREIGN_KEY_CHECKS=1');
    }
}

0 likes
29 replies
jrdavidson's avatar

Thanks for the input I wonder if there might be any other suggestions as well.

bobbybouwmann's avatar

You can take your concept and the concept of the video and combine them right? I think you have all the pieces for puzzle here ;)

ricardoarg's avatar

Just put some created_by and updated_by in the model. Then on the creating and updating events in that model, set that fields to Auth::user()->id, and done!

class UserAccount extends Model
{
    protected static function boot()
    {
        parent::boot();
    static::creating(function ($model) {
        $model->created_by = Auth::user()->id;          
    });
   }

}
jrdavidson's avatar

@bobbybouwmann and @emprenet

I am struggling with adding making this work because if I had lets say created_by fields for example then when I do my seed files I continue to get foreign constraint violations no matter the other I call my seed files in.

First thing I'm wanting to do is make sure I assign what user created all my model instances. Now should I have a created_by field and/or/both just keep it the way @JeffreyWay does it in the activity feed video with assigning it just in the activities table.

jrdavidson's avatar
<?php

use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Model;

class DatabaseSeeder extends Seeder
{
    private $tables = [
        'roles',
        'permissions',
        'permission_role',
        'user_accounts',
        'user_accounts_profiles',
        'user_accounts_statuses',
    ];

    private $seeders = [
        'UserAccountsTableSeeder',
        'UserAccountStatusesTableSeeder',
       'RolesAndPermissionsTablesSeeder'
    ];

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $this->cleanDatabase();

        Model::unguard();

        foreach($this->seeders as $seeder)
        {
            $this->call($seeder);
        }

        Model::reguard();
    }

    private function cleanDatabase()
    {
        DB::statement('SET FOREIGN_KEY_CHECKS=0');

        foreach ($this->tables as $tableName)
        {
            $this->command->info('Deleting existing ' . $tableName . ' data ...');
            DB::table($tableName)->truncate();
        }

        DB::statement('SET FOREIGN_KEY_CHECKS=1');
    }
}
<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('user_accounts', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('first_name');
            $table->string('last_name');
            $table->string('username')->unique();
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->string('avatar')->unique()->nullable();
            $table->integer('status_id')->unsigned()->default(1);
            $table->integer('role_id')->unsigned();
            $table->rememberToken();
            $table->integer('created_by')->unsigned();
            $table->integer('updated_by')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('user_accounts_profiles', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('user_id')->unsigned()->index();
            $table->string('biography')->nullable();
            $table->string('address')->nullable();
            $table->string('city')->nullable();
            $table->string('state')->nullable();
            $table->integer('postcode')->nullable();
            $table->string('country')->nullable();
            $table->string('phone')->nullable();
            $table->timestamp('birthday')->nullable();
            $table->string('facebook_username')->unique()->nullable();
            $table->string('twitter_username')->unique()->nullable();
            $table->string('google_plus_username')->unique()->nullable();
            $table->string('behance_username')->unique()->nullable();
            $table->string('pinterest_username')->unique()->nullable();
            $table->string('linkedin_username')->unique()->nullable();
            $table->string('github_username')->unique()->nullable();
            $table->string('youtube_username')->unique()->nullable();
            $table->string('instagram_username')->unique()->nullable();
            $table->string('external_link')->unique()->nullable();
            $table->integer('created_by')->unsigned();
            $table->integer('updated_by')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('user_accounts_statuses', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->unique();
            $table->string('label')->unique();
            $table->integer('created_by')->unsigned();
            $table->integer('updated_by')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('user_accounts');
        Schema::drop('user_accounts_profiles');
        Schema::drop('user_accounts_statuses');
    }
}
<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateRolesAndPermissionTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('roles', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name')->unique();
            $table->string('label')->unique()->nullable();
            $table->integer('created_by')->unsigned();
            $table->integer('updated_by')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('permissions', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name')->unique();
            $table->string('label')->unique()->nullable();
            $table->integer('created_by')->unsigned();
            $table->integer('updated_by')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('permission_role', function(Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->integer('permission_id')->unsigned();
            $table->integer('role_id')->unsigned();
            $table->integer('created_by')->unsigned();
            $table->integer('updated_by')->unsigned();
            $table->timestamps();
            $table->softDeletes();

            $table->primary(['permission_id', 'role_id']);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('roles');
        Schema::drop('permissions');
        Schema::drop('permission_role');
    }
}
<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AddForeignKeysToUsersTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('user_accounts', function ($table) {
            $table->foreign('status_id')
                ->references('id')
                ->on('user_accounts_statuses')
                ->onDelete('cascade');

            $table->foreign('role_id')
                ->references('id')
                ->on('roles')
                ->onDelete('cascade');
        });

        Schema::table('user_accounts_profiles', function ($table) {
            $table->foreign('user_id')
                ->references('id')
                ->on('user_accounts')
                ->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('user_accounts', function ($table) {
            $table->dropForeign(['status_id']);
            $table->dropForeign(['role_id']);
        });

        Schema::table('user_accounts_profiles', function ($table) {
            $table->dropForeign(['user_id']);
        });
    }
}
d3xt3r's avatar

Make the created_by and updated_by fields nullable, as our creator is one of your user. When creating the creator/administrator account you will require to add this field if not nullable and will throw the constraint error.

Also can you show the seed file where you get the error, at what step?

jrdavidson's avatar
  1. Is it only on the user_accounts table that I make the created_by and updated_by fields nullable?

I ask because all users and other models will have a created_by filled in at some point. Whether it's by seeding or by a form being processed.

  1. Okay so what table should I be seeding first?
jrdavidson's avatar

I'm sorry I continue to ask questions but trying to understand why I don't create the created_by and updated_by for all my other models?

d3xt3r's avatar
  1. I didn't say don't create, i said no need to make them nullable for other classes, as you asked.

But IMO, its better to have a morphable logger class, so that even the activity history is maintained, if the need may be ?

  1. User table should be one to be seeded first as a reference is required for other tables.
d3xt3r's avatar

Nevermind... An activity class should suffice ... meant the same but with polymorphic relation (not much needed)

jrdavidson's avatar

Okay great. With knowing this what would you recommend me handling all of this now?

d3xt3r's avatar

Probably you wont like this :) , but it all depends on your need.

If you continuously need to show created_by , updated_by fields, its efficient to have them in the same table as model.

If only for the purpose of monitoring, and in order to blame someone, if things go wrong, an activity table as in the videos is great.

Also, if you are enforcing foreign keys, going with first option, you strictly need to adhere to them, i.e. you need to have an entry in the foreign table (with same id) before you add an entry to the main table.

jrdavidson's avatar

So your saying I either need to do the created_by and updated_by fields OR use the activity table? Its an either or.

d3xt3r's avatar

If both ifs are applicable to you, you can have an AND as well.

jrdavidson's avatar

Really? If that's the case then are my migrations correct? And then my seed files?

d3xt3r's avatar

I thinks we have digressed from OP. What is the error that you are getting, if its only the foreign key constraint, are you specifying it when saving the model ?

d3xt3r's avatar

Are you getting any error with your current approach ? If yes, what? As far as i can tell, with current migrations, your seeds won't work unless taken care of in model.

Also, show the model class, so that we may have a look at the implementation.

jrdavidson's avatar

Currently my error says this:

Illuminate\Database\QueryException]
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`myapp`.`user_accounts`, CONSTRAINT `user_accounts_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `user_accounts_statuses` (`id`) ON DELETE CASCADE) (SQL: insert into `user_accounts` (`first_name`, `last_n
  ame`, `username`, `email`, `password`, `avatar`, `status_id`, `role_id`, `remember_token`, `created_by`, `updated_at`, `created_at`) values (John, Doe, jdoe, john@example.com, y$e7ye9vBvEtDt/zodgMD/n.YYKcTtH3uxIgNVXSuKwo18WSMWgHRqi, jdoe.jpg, 1, 5, Ho54bpIns7, 1, 2016-03-16 21:17:23, 2016-03-16 21:17:23))

  [PDOException]
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`myapp`.`user_accounts`, CONSTRAINT `user_accounts_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `user_accounts_statuses` (`id`) ON DELETE CASCADE)
d3xt3r's avatar

In my last attempt to explain this, If you need to be sure how foreign keys / constraint, i suggest read some material. In a nut shell, these are ids(keys) which refer to a row in a different(even same) table associated with an id. These ids can be null, or be something which is valid (i.e. one that exists).

While seeding if you circle around these violations, its better to define it as nullable.

As as example.

$myUser = factory(UserAccount::class)
            ->create([
                'first_name' => 'John',
                'last_name' => 'Doe',
                'username' => 'jdoe',
                'email' => 'john@example.com',
                'password' => 'testpass123',
                'avatar' => 'johndoe.jpg',
                'status_id' =>  1, // If its a foreign key, this will fail if no status exists with id = 1, so first need to create this
                'role_id' => 5, // If its a foreign key, this will fail if no role exists with id = 5, so first need to create this
                'creator_id' => 1 //If its a foreign key, this will fail if no user exists with id = 1, so first need to create this
            ]);

However for creating a user, role and status, you again require creator(user), so its better to have the fields status_id,role_id,creator_id as nullable in user table, or the creator_id field in roles or statuses table as nullable, whichever suits you.

But the zest is that you need to adhere to the constraints.

1 like
jrdavidson's avatar

Now I understand that but where does the activities come into play when this is happening.

[Illuminate\Database\QueryException]
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`myapp`.`activities`, CONSTRAINT `activities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user_accounts` (`id`) ON DELETE CASCADE) (SQL: insert into `activities` (`subject_id`, `subject_type`, `name`, `user
  _id`, `updated_at`, `created_at`) values (1, App\UserAccountStatus, created_useraccountstatus, 1, 2016-03-16 21:36:46, 2016-03-16 21:36:46))
d3xt3r's avatar
// You have a foreign constraint.
CONSTRAINT `activities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user_accounts` (`id`) ON DELETE CASCADE

// and then you try to insert a record with user_id = 1, does this user exists ???

insert into `activities` (`subject_id`, `subject_type`, `name`, `user
  _id`, `updated_at`, `created_at`) values (1, App\UserAccountStatus, created_useraccountstatus, 1, 2016-03-16 21:36:46, 2016-03-16 21:36:46))

jrdavidson's avatar

I can't do this because you said I had to put the statuses first. This is what I"m having issues with. If I try it one way I get issues if I try it another way I get more issues.

d3xt3r's avatar

I also mentioned,

However for creating a user, role and status, you again require creator(user), so its better to have the fields status_id,role_id,creator_id as nullable in user table, or the creator_id field in roles or statuses table as nullable, whichever suits you.

jrdavidson's avatar

I guess I can do that and then go back through and update the values with 1 after the user gets created

Please or to participate in this conversation.