One post belonging to many businesses

I have a database with a structure similar to the one below (apologies if the diagram isn't fully clear).

The statistics, favourite_likes and category_links all have polymorphic relationships with the posts table. A business can have many posts, and a post can have many dates.

The post will only appear on the frontend of the site if it has a date in the post dates table which is in the future.

The issue is I need to change this design so that its possible for one post to be linked to many businesses, this seems fairly straightforward however there are many issues:

Each post and business have a coords spatial column, this column is used for a nearest me search on the frontend of the website. The coords column is optional for the user in the backend if they don't enter anything the system will just copy the coords from the business. So if I have a post linking to many businesses then it will need to store more than one set of coordinates.

Furthermore our users on the site will want to see how a post is performing in different areas, this is currently possible because each post links to one row of statistics. If I have one post linking to many businesses however the users will just see an overall statistic for the post not per location.

Our DB is very complicated and I need a method of enabling a post to link to many businesses which is simple and doesn't drastically complicate things anymore. The system is built in Laravel so Eloquent makes it easier to set relationships.

Some assistance would be greatly appreciated!

What exactly is your question?
– lxg
Jun 29 at 10:58

1 Answer
1

You can define ManyToMany relation between post and business with moving cords column from posts table to pivot table, so that you have cords for each post and business relation.

cords

post

business

Post Model

public function businesses(){
return $this->belongsToMany(Business::class, 'business_post')->withPivot('cords');
}

public function statistic(){
return $this->hasOne(Statistic::class);
}

public function dates(){
return $this->hasMany(PostDate::class);
}


pivot table business_post(id, post_id, business_id, cords)

Business Model

public function posts(){
return $this->belongsToMany(Post::class, 'business_post')->withPivot('cords');
}


Fetch data

$posts = Post::with('businesses','statistic','dates')->get();


Similarly you can define for other models

Details https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

Cheers I didn't realise you could have pivot tables like that, looking at my model and the db structure this methods should work fine.
– Luke.T
Jun 29 at 12:34

glad it may work for your design, happy coding
– rkj
Jun 29 at 12:35

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.