Polymorphic or Separate Tables?

I am looking for some suggestions on the best way to structure a part of my database...

My application has Content, which is created by Users. It has Users, which can belong to Groups, or be assigned to Positions. There are 3 ways the Content can be shared:

To the User(s).
To the Group(s).
To the Position(s).

Should I have individual tables for each (e.g. content_user, content_group, content_position) or create a polymorphic table (e.g. content_shared) and put everything in there? Or is there a different way you would suggest?

Something to add. As Users are added / removed from Groups or Positions their access to shared Content would need to change.

And... how would you recommend I go about writing that query?

Thanks for the input!

LaryAI

3 years ago

Level 58

In this case, it would be best to use a polymorphic table to store the shared content. This will allow for more flexibility in the future if additional sharing options are added.

The table could be named something like "content_shares" and have columns for the shared content's ID, the type of sharer (user, group, or position), and the ID of the sharer.

Here's an example migration for the "content_shares" table:

Schema::create('content_shares', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('content_id');
    $table->string('sharer_type');
    $table->unsignedBigInteger('sharer_id');
    $table->timestamps();

    $table->foreign('content_id')->references('id')->on('content');
    $table->index(['sharer_id', 'sharer_type']);
});

Then, to retrieve the shared content for a user, group, or position, you can use a polymorphic relationship. For example, to get all content shared with a user:

class User extends Model
{
    public function sharedContent()
    {
        return $this->morphedByMany(Content::class, 'sharer', 'content_shares');
    }
}

$user = User::find(1);
$sharedContent = $user->sharedContent;

vincent15000

3 years ago

Best Answer

Level 63

I would use separate tables, but you can also create a common one to shared the pivot table with the three models.

I think that using separate tables is a good idea because if your application evolves and you need to add some custom properties inside the pivot tables, you can more easily add different custom properties in each pivot table.

dmhall0

3 years ago

Level 1

@vincent15000 Thank you for the feedback. I am wondering though if this would make CRUD'ing the relationships more difficult as I would need a unique function for each?

1 like

vincent15000

3 years ago

Level 63

@dmhall0 That's a choice you have to do. Having tested both solutions, I even that it's more easy to have multiple pivot tables. And even in this case you have a unique function provided by Laravel to bind the relationships to the parent model.

For example.

$post->comments()->attach(...);

$image->comments()->attach(...);

$book->comments()->attach(...);

You can use attach() or sync() or one of the other functions provided by Laravel according to your needs.

dmhall0

3 years ago

Level 1

@vincent15000 Thank you. I'll give this direction a try.

1 like

Please or to participate in this conversation.