One-to-many relationship with a variable table

Question

So, I have a TV Show's website, and I currently have the following tables structure (only listing the relevant columns):

Table 1 - TV Shows
id

Table 2 - Episodes
id

Table 3 - Comments 
target
value

Both TV Shows and Episodes can have comments, so the "target" column specifies if it is a "TV Show" or a "Episode" comment, and the "value" column specifies its ID.

I already thought of merging both columns and using "x_ID" ("tvshow_id" or "episode_id", like: "tvshow_45" or "episode_9458").

But what I really want to know is: what is the best way to do this? With both ways I described, I can't use foreign keys (I don't know the benefit of this, but I guess it's good). And another problem is to use Laravel's Eloquent feature...

I could just create two comments tables, but I don't know if it's the only or best way...

Can someone help me manage this?

Thank you in advance.

Gabe.

Answer 1

The way it works in laravel, you use commentable_* columns:

id, commentable_id, commentable_type, value

Then you use a polymorphic relationship, so that Eloquent handles it all for you.

---

If you're using migrations, laravel can generate those columns for you:

Schema::create('comments', function($table)
{
    $table->increments('id');
    $table->morphs('commentable');
    $table->string('value');
});

It'll even automatically create a composite index for you!