Twitterクローン DB設計

環境

• Windows
• Homestead
• Laravel 7.3.1

要件

• ログインユーザのみ利用可能
• ツイート投稿機能
• リプライといいね機能
• フォロー機能

DB設計

• usersテーブル
  • ユーザ管理テーブル
• tweetsテーブル
  • ユーザ毎のツイート管理テーブル
• commentsテーブル
  • コメント管理テーブル
• favoritesテーブル
  • いいね管理テーブル
• followersテーブル
  • フォロー関係管理テーブル

Migrationファイルの作成

make:model 〇〇 -m とすることでModelとMigrationを同時作成。

php artisan make:model Tweet -m
php artisan make:model Comment -m
php artisan make:model Favorite -m
php artisan make:model Models/Follower -m

Users

2014_10_12_000000_create_users_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateUsersTable extends Migration
{
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('users', function (Blueprint $table) {
 $table->increments('id');
 $table->string('screen_name')->unique()->null()->comment('アカウント名');
 $table->string('name')->null()->comment('ユーザ名');
 $table->string('profile_image')->nullable()->comment('プロフィール画像');
 $table->string('email')->unique();
 $table->timestamp('email_verified_at')->nullable();
 $table->string('password');
 $table->rememberToken();
 $table->timestamps();
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::dropIfExists('users');
 }
}

Tweets

2020_10_24_2358_create_tweets_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTweetsTable extends Migration
{
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('tweets', function (Blueprint $table) {
 $table->increments('id');
 $table->unsignedInteger('user_id')->comment('ユーザID');
 $table->string('text')->comment('本文');
 $table->softDeletes();
 $table->timestamps();
 $table->index('id');
 $table->index('user_id');
 $table->index('text');
 $table->foreign('user_id')
 ->references('id')
 ->on('users')
 ->onDelete('cascade')
 ->onUpdate('cascade');
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::dropIfExists('tweets');
 }
}

以下でUsersテーブルと外部キー接続を宣言。

2020_10_24_2358_create_tweets_table.php

 $table->foreign('user_id')
 ->references('id')
 ->on('users')
 ->onDelete('cascade')
 ->onUpdate('cascade');

Comments

2020_10_24_2358_create_comments_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCommentsTable extends Migration
{
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('comments', function (Blueprint $table) {
 $table->increments('id');
 $table->unsignedInteger('user_id')->comment('ユーザID');
 $table->unsignedInteger('tweet_id')->comment('ツイートID');
 $table->string('text')->comment('本文');
 $table->softDeletes();
 $table->timestamps();
 $table->index('id');
 $table->index('user_id');
 $table->index('tweet_id');
 $table->foreign('user_id')
 ->references('id')
 ->on('users')
 ->onDelete('cascade')
 ->onUpdate('cascade');
 $table->foreign('tweet_id')
 ->references('id')
 ->on('tweets')
 ->onDelete('cascade')
 ->onUpdate('cascade');
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::dropIfExists('comments');
 }
}

Favorites

2020_10_24_2358_create_favorites_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateFavoritesTable extends Migration
{
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('favorites', function (Blueprint $table) {
 $table->increments('id');
 $table->unsignedInteger('user_id')->comment('ユーザID');
 $table->unsignedInteger('tweet_id')->comment('ツイートID');
 $table->index('id');
 $table->index('user_id');
 $table->index('tweet_id');
 $table->unique([
 'user_id',
 'tweet_id'
 ]);
 $table->foreign('user_id')
 ->references('id')
 ->on('users')
 ->onDelete('cascade')
 ->onUpdate('cascade');
 $table->foreign('tweet_id')
 ->references('id')
 ->on('tweets')
 ->onDelete('cascade')
 ->onUpdate('cascade');
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::dropIfExists('favorites');
 }
}

Followersテーブル

自分がフォローしているユーザのツイートをTLに表示する際は
自分がfollowing_idで相手がfollowed_idとなる。

2020_10_24_2358_create_followers_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateFollowersTable extends Migration
{
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('followers', function (Blueprint $table) {
 $table->unsignedInteger('following_id')->comment('フォローしているユーザID');
 $table->unsignedInteger('followed_id')->comment('フォローされているユーザID');
 $table->index('following_id');
 $table->index('followed_id');
 $table->unique([
 'following_id',
 'followed_id'
 ]);
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::dropIfExists('followers');
 }
}

Model

Users

app/User

<?php
namespace App;
use Illuminate\Notifications\Notifiable;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
class User extends Authenticatable
{
 use Notifiable;
 /**
 * The attributes that are mass assignable.
 *
 * @var array
 */
 protected $fillable = [
 'screen_name',
 'name',
 'profile_image',
 'email',
 'password'
 ];
}

Tweets

app/Tweet.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\softDeletes;
class Tweet extends Model
{
 use SoftDeletes;
 /**
 * The attributes that are mass assignable.
 *
 * @var array
 */
 protected $fillable = [
 'text'
 ];
}

Comments

app/Comment.php

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\softDeletes;
class Comment extends Model
{
 use SoftDeletes;
 /**
 * The attributes that are mass assignable.
 *
 * @var array
 */
 protected $fillable = [
 'text'
 ];
}

Favorites

app/Favorite.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Favorite extends Model
{
 public $timestamps = false;
}

Followers

app/Follower.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Follower extends Model
{
 protected $primaryKey = [
 'following_id',
 'followed_id'
 ];
 protected $fillable = [
 'following_id',
 'followed_id'
 ];
 public $timestamps = false;
 public $incrementing = false;
}

リレーションの親子関係

Users

app/User.php

 //追記
 public function followers()
 {
 return $this->belongsToMany(self::class, 'followers', 'followed_id', 'following_id');
 }
 public function follows()
 {
 return $this->belongsToMany(self::class, 'followers', 'following_id', 'followed_id');
 }

Tweets

app/Tweet.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\softDeletes;
class Tweet extends Model
{
 use SoftDeletes;
 /**
 * The attributes that are mass assignable.
 *
 * @var array
 */
 protected $fillable = [
 'text'
 ];
 public function user()
 {
 return $this->belongsTo(User::class);
 }
 public function favorites()
 {
 return $this->hasMany(Favorite::class);
 }
 public function comments()
 {
 return $this->hasMany(Comment::class);
 }
}

Comments

app/Comment.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\softDeletes;
class Comment extends Model
{
 use SoftDeletes;
 /**
 * The attributes that are mass assignable.
 *
 * @var array
 */
 protected $fillable = [
 'text'
 ];
 public function user()
 {
 return $this->belongsTo(User::class);
 }
}

Migration

php artisan migrate