-1

Currently, I have Post model having these hasMany with inverse relationships assigned to it.

public function category()
{
    return $this->belongsTo(Category::class);
}

public function group()
{
    return $this->belongsTo(Group::class);
}

public function user()
{
    return $this->belongsTo(User::class);
}

public function permissions()
{
    return $this->hasMany(Permission::class);
}

public function comments()
{
    return $this->hasMany(Comment::class);
}

Also, I'm planning to split some other columns (thumbnail, tags, video, approval status, etc) and move all these things into a many-to-many relationship.

For example:

// Post Model
protected $fillable = ['user_id', 'category_id', 'group_id', 'title', 'slug', 'description', 'thumbnail', 'tags', 'video', 'approved_status'];

I know that my database will be two or three times bigger but the question is what will be the performance: very fast, fast, average, slow, very slow, etc.

Like on every page except Comments, Tags, and Videos, all the M2M relationships should be eagerly loaded with corresponding data from the query of model Post to show posts related data on blogging sites.

Looking for pieces of advice for best practices.

What are your thoughts on it?

JS TECH
  • 1,556
  • 2
  • 11
  • 27

1 Answers1

1

From my experience with Laravel there are a few things to take into account. One the orm and database system. Second, project goals and mvp in consideration to database performance. To me it sounds like this really should not be that bad. Here is my take on what you are describing from my point of view.

Looking at the changes you want to make I have a few questions about dependency in terms of database relationships matching your model structure.

For Example, a situation where you have a Store, customers, products, orders.

Customers has an underlying dependency. In which cannot exist without a store and neither can products. The same goes for orders dependency on products, customers, and store. This I would assume to be the business logic for the most part in the application. I tend to when I design database tables and relationships try to match those together in two ways. First I ensure the logical flow exists (the dependency) in both the app code and database design. Secondly, in terms of quick out of the gate performance wins to a quick peek into problems that become prevalent with scale. typically, this is in relation to Big O notation, but in a business sense I kind of ask my self how many zeros can I add to the number of rows in a table until I start seeing drastic storage costs or drastic reduction in performance.

Given that, I think there are two potential wins here for you. Now remember I don't fully know the whole use case, but it seems to me that something like Category and Group maybe something that exist as part of a User driven need and not something driven by a post. That meaning does the User place them selves into a category andor group? Or does the post that they made exist in a category or group. I would assume one of those cases it slides back to the user. Meaning that a user is in a group posting in a category. If these are not many to many and they are one to many meaning that a user can only post to one group in one category the user should own that relationship. Therefore moving those relationships off the Post model and onto the User model. However, if they are many to many, I think you have a good opportunity to create a lookup, pivot table and gain some performance. In laravel that may be best done through a ManyToManyThrough or ManyToMany. Where you can create a table for quickly finding those relationships and as well as keeping the history of the post. Therefore you may have ID, user_id, group_id, category_id, post_id, created_at, updated_at. However, still think about filtering the size of the data set. So obviously this table is going to grow at the rate of posts I would imagine. If so is there any set of information that one could ask themselves at a given time in the application that they have to get to that post. Therefore looking in smaller tables first or even smaller lookup tables. Meaning chances are Group or category will probably be the smallest sized tables in that bunch. Therefore filtering first by group & category most likely the list of posts is manageable even at scale. Sorry have to get to work, but

Generally, you are looking at potentially creating three separate relationships and to find those relationships you have to search every post record in the post table which may be the biggest table in your system. Therefore you goal would be to shrink that data set. This comes down to performance and space capacity in terms of costs. space is typically less but huge data sets can be expensive when needed nearline (cached). If that is the case then if you were to split those three relatable relationships in to 3 pivot tables you are going to create redundancy. Nx4 and in terms of attempting to find a comment on that post Nx4xcomments and potentially how you handle them. Therefore by creating one table that holds all their relationships and to be honest for comments I would create something called CommentCollection that has an comment_collection_id on that post table so that you can find your comments quickly as well. You are going to need to optimize further than design really even considers though in consideration to scale. Therefore utilizing things like views, GCP/AWS/Azue and some of their fast infrastructure tools for data would most likely become a requirement. Not to mention the video data I assume you would have as well. Sorry gotta go. Just kind of spitballing and sharing some of my bumps in the road.

amac
  • 921
  • 1
  • 6
  • 17