Better way to save array / objects in PHP Laravel MySQL-CodePudding

What is the better practice for example I have a table of Posts and the post might contain many comments, is it better to create a Comments table and use a foreign ID or create a column of comments in the Posts table and save it as an array and cast it in the Model? It would be like an array of objects

[{"username":"user1","date":"25 February 2022 01:01PM","comment":"qwer"}]

    protected $casts = [
        'comments' => 'array',
    ];

CodePudding user response：

depend on how you're gonna use the comments.

For example if you want to retrieve a post with only the count of the comments instead of their content, then you will need to put them in another table to make retrieving that info faster and effortless.

// for example
Post::withCount('comments')->get();

If every time you need a post, you also need its comments, then there is no reason to put them in another table. But you will be limiting yourself for future evolution without a heavy cost on performance because it would go against database normalization.

CodePudding user response：

It's better to create a comments table and Comment Model with relation between your existing Post model and Comment model.

In case comment will only be attached to Post you can define a one to many relationship

Here is the definition of the Post Model

class Post extends Model
{
    /**
     * Get the comments for the blog post.
     */
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

And here is the definition of Comment

class Comment extends Model
{
    /**
     * Get the comments for the blog post.
     */
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

with this you must have a foreign key on the comments table named post_id which reference the id in the posts table.

With this relationship established you can get all comment for a given post by simple calling $post->comments or access post from comment by calling $comment->post You can learn more here Eloquent: Relationship - One to Many