Advice on my tables for p3?

pllealfunes commented 2 years ago

Hey guys,

For my p3 I would like to make a blog where users can comment and post. I think I have an idea if what my tables should look like but I wanted to post it here to see if anyone has an idea on how it can be improved or if I am on the right track.

Thanks!

Users Table from Fortify

public function up()
{
   Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

New Migration to connect Users table to Post Table + Comments Table

public function up()
{
    Schema::table('users', function (Blueprint $table) {

        $table->bigInteger('comment_id')->unsigned()->nullable();
        $table->bigInteger('post_id')->unsigned()->nullable();

        $table->foreign('comment_id')->references('id')->on('comments');
        $table->foreign('post_id')->references('id')->on('posts');

    });
}

public function down()
{
    Schema::table('users', function (Blueprint $table) {

        # ref: http://laravel.com/docs/migrations#dropping-indexes
        # combine tablename + fk field name + the word "foreign"
        $table->dropForeign('users_comment_id_foreign');
         $table->dropForeign('users_post_id_foreign');

        $table->dropColumn('comment_id');
        $table->dropColumn('post_id');
    });
}

Comments Table

public function up()
{
    Schema::create('comments', function (Blueprint $table) {

        $table->id();
        $table->timestamps();
        $table->text('comment');
         $table->foreign('user_id')->references('id')->on('users');
    });
}

public function down()
{
    Schema::dropIfExists('comments);
}

Posts Table

public function up()
{
    Schema::create('posts', function (Blueprint $table) {

        $table->id();
        $table->timestamps();
        $table->string('title');
        $table->string('category');
        $table->text('post);
         $table->foreign('user_id')->references('id')->on('users');
    });
}

public function down()
{
    Schema::dropIfExists('posts');
}

susanBuck commented 2 years ago

Plan looks good to me 👍 Only thing I noticed is the pivot table still has users as the name of the table in the migration - assuming that was just a copy/paste error though : )

pllealfunes commented 2 years ago

I thought in the beginning it would be a one to many relationship which is why the table says Users and I would update Users table from Fortify by using the notes in the One to Many notes where the Books table connects to the Authors table.

Since Users will connect to Posts and Comments it's more of a many to many relationship?

Should the pivot table look like the below? I looked at the notes again and should I create a pivot table for each connection? So, one for (EX 1)users and comments then (EX 2) one for users and posts?

EX 1


public function up()
{
Schema::create('comment_user', function (Blueprint $table) {

    $table->id();
    $table->timestamps();

    $table->bigInteger('comment_id')->unsigned();
    $table->bigInteger('user_id')->unsigned();

    $table->foreign('comment_id')->references('id')->on('comments');
    $table->foreign('user_id')->references('id')->on('users');

});
}

public function down() { Schema::dropIfExists('comment_user'); }


### EX 2

public function up() { Schema::create('post_user', function (Blueprint $table) {

    $table->id();
    $table->timestamps();

    $table->bigInteger('post_id')->unsigned();
    $table->bigInteger('user_id')->unsigned();

    $table->foreign('post_id')->references('id')->on('posts');
    $table->foreign('user_id')->references('id')->on('users');

});

}

public function down() { Schema::dropIfExists('comment_user'); }


2. I forgot to add that I wanted to connect my posts to my comments table so that each post shows a list of comments from users. Should that be another pivot table? Then add `$table->foreign('post_id')->references('id')->on('posts) ` into the comments table? And a `$table->foreign('comment_id')->references('id')->on('comments') ` into my posts table?

Pivot Table

public function up() { Schema::create('comment_post', function (Blueprint $table) {

    $table->id();
    $table->timestamps();

    $table->bigInteger('comment_id')->unsigned();
    $table->bigInteger('post_id')->unsigned();

     $table->foreign('comment_id')->references('id')->on('comments');
    $table->foreign('post_id')->references('id')->on('posts');

});

}

public function down() { Schema::dropIfExists('comment_post'); }

pllealfunes commented 2 years ago

Now I think the Posts to Comments would be more of a one to many relationship...so would that mean I would have to do the below instead?

public function up()
{
    Schema::table('posts', function (Blueprint $table) {

        $table->bigInteger('comment_id')->unsigned()->nullable();

        $table->foreign('comment_id')->references('id')->on('comments');

    });
}

public function down()
{
    Schema::table('books', function (Blueprint $table) {

        # ref: http://laravel.com/docs/migrations#dropping-indexes
        # combine tablename + fk field name + the word "foreign"
        $table->dropForeign('posts_comments_id_foreign');

        $table->dropColumn('comment_id');
    });
}

I'm sorry I thought I had understood how to handle relationships but I guess my a little confused.

susanBuck commented 2 years ago

Hi @pllealfunes -

I think I read your initial post too fast, as I'm now amending my answer. : )

If I'm understanding your goals correctly, I believe the following is what you need. Let me know if this makes sense or if I'm misinterpreting your goals.

Table: `posts`

Fields:

title (string)
category (string)
post (text)
user_id (bigint, FK to users table)

Goal: users can author posts

Notes: This table uses a one to many relationship, where one user can have many posts.

Table: `comments`

Fields:

comment (text)
user_id (bigint, FK to users table)
post_id (bigint, FK to posts table)

Goal: users can create comments, and each comment is associated with a post.

Notes: This table uses a many to many relationship, connecting many users to many posts.

Technically, this is a pivot table connecting users and posts, with the additional field of comment, so you might name it post_user following the naming conventions of pivot tables.

However, in this case I think it's logical to simply call it comments since that's more descriptive. Here are instructions on how to set a custom table name in the model.

pllealfunes commented 2 years ago

Hi @susanBuck

Yes this is exactly what I want for my project. Thank you so much for the help and the explanations on the relationships :)

pllealfunes commented 2 years ago

Hi @susanBuck

I'm confused on the custom name part for my comments table. I created my comments table but for the Comment Model do I have to do the below for the custom name table?

Or was I supposed to create the table as post_user and then add the custom name to the PostUser model?

class Comment extends Model
{
    use HasFactory;
            /**
     * The table associated with the model.
     *
     * @var string
     */

    protected $table = 'comments';

     public function users()
    {

        return $this->belongsToMany('App\Models\User')
        ->withTimestamps(); 
    }

}

susanBuck commented 2 years ago

Hi @pllealfunes -

Correcting my previous reply - I linked to the wrong docs - where you need to customize the table name is when you're defining the pivot relationship in the User and Post model.

For example, if you didn't customize the table name and did something like this...

User.php:

public function posts() {
    # This relationship will, by default/convention, be established via a pivot table called `post_user`
    return $this->belongsToMany(Post::class);
}

This would assume the users <-> posts relationship was made via a pivot table called post_user. This is what we want to override. We do this by passing the custom pivot table name as the second argument of the belongsToMany relationship like so:

User.php:

public function posts() {
    # This relationship will be established via a customized pivot table called `comments`
    return $this->belongsToMany(Post::class, 'comments');
}

Similarly, in Post.php:

public function users() {
    return $this->belongsToMany(User::class, 'comments');
}

Here's the relevant part from the docs explaining the above:

https://laravel.com/docs/9.x/eloquent-relationships#many-to-many

pllealfunes commented 2 years ago

Awesome, thank you!

susanBuck / e15-spring22