Laravel 5.3, MySQL, proper foreign key setup in migrations (one to many, many to many)

Question

what is the proper way to setup the foreign key restrains in following scentarios.

Let say I have models Post, User, Tag.

At the moment my migrations are all set simply as:

For Comment model:

$table->integer('posts_id')->unsigned(); //comment must belong to a post
$table->integer('user_id')->nullable()->unsigned(); //comment can belong to an user
$table->integer('something_id')->unsigned(); // each Something model will have at most 5 to 10 comments in it - this is what is called cardinality if I got it right so the average cardinality is 7.5

and let's say for a many to many relation I have a pivot table between Posts and Tags as:

$table->integer('post_id')->unsigned(); //connecting the post
$table->integer('tag_id')->unsigned(); //connecting the tag

Now I need to index the fields so when my db queries are executed they're executed as fast as possible.

What I would do now is create new migration as:

$table->foreign('posts_id')->references('id')->on('posts');
$table->foreign('user_id')->references('id')->on('users'); 
$table->foreign('something_id')->references('id')->on('somethings');

And the same for the pivot table:

$table->foreign('post_id')->references('id')->on('posts');
$table->foreign('tag_id')->references('id')->on('tags');

My questions are:

• Is this the proper way to do this, if not how should I do it instead?
• Is there some restrictions, downsides or anything I should be aware of here?
• Would I gain anything by doing $table->foreign('something_id')->references('id')->on('somethings'); as the cardinality here is about 7.5? Updating of comments.something_id will never happen in my app, and the ratio of "creation of new something row" to "reading related something row from comment row" would be really low 1:10^5 (rough estimate and in next row of optimizations I'll work on caching the queries so not sure how relevant this information is)

Answer 1

You do not need to create new migration file for defining foreign key.

$table->foreign('posts_id')->references('id')->on('posts');
$table->foreign('user_id')->references('id')->on('users'); 
$table->foreign('something_id')->references('id')->on('somethings');

put those inside comment table migration file. And

$table->foreign('post_id')->references('id')->on('posts');
$table->foreign('tag_id')->references('id')->on('tags');

in pivot table migration file.