Database Design tip.

Table structure should be

hotels

id
name

hotel_rooms

id
hotel_id
name

hotel_packages

id
hotel_room_id
name

hotel_package_rates

id
hotel_package_id
value
started_at
ended_at

You can use Has Many Through relationship for that

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Hotel extends Model
{
    public function packages()
    {
        return $this->hasManyThrough('App\Models\HotelPackage', 'App\Models\HotelRoom');
    }
}

To get packages by hotel:

$hotel = Hotel::with('packages')->findOrFail(100);

$packages = $hotel->packages;

To get rates by hotel:

$hotel = Hotel::with('packages')->findOrFail(100);

$packageRates = HotelPackageRate::wherenIn('hotel_package_id', $hotel->packages->pluck('id'))->get();

Docs: https://laravel.com/docs/8.x/eloquent-relationships#has-many-through

I am aware of that @michaloravec . But behind the scene it will traverse through all tables right? For example, in order to get all packages of hotel 1, first it will fetch room IDs and then based on those IDs, fetch all packages.

In a very large project, where this tree of interrelated tables is large and the queries are also complex, this will be a bit slower compared to the second approach. I have already seen this happening, even after having a database design with good indexing.

So my question is that, is it feasible to go for first approach and ignore the logical inconsistency factor just for the sake of good performance ?

It's not big different, Database is optimazed to work with primary and foreign keys. Also everytime cache your results from the database.

That's it.

Docs: https://laravel.com/docs/8.x/cache

With the correct indexes it's not a big chore for the database.

thanks @michaloravec @tray2