Lavavel Scheme to handle Properties and Rooms?

Question

I'm not sure how to best structure my tables so that the storage and querying is efficient and easy..

This is what i have:

• rooms table - containing 10+ different room types (Kitchen, Lounge, etc), (with a unique ID)
• properties_rooms lookup table - the Properties ID and the Room_id
• properties table - containing many different properties (with a unique ID)

In addition to the above, i need to be able to capture a specific note and level attribute for each Property/Room combination, for example:

• (Property ID - '1') -> from properties table
• (Property Name -'Sunny House')-> from properties table
• (Room ID - '2')-> from properties_rooms lookup
• (Room Name - 'Bedroom')-> from Rooms table
• (Level - "Ground Floor")-> what table??
• (Note - "Twin beds")-> what table??

Level and Note above are specific to the combination of the property and the room type, so seem to make sense to be in the lookup?

I have the following scheme setup:

Properties

Schema::create('properties', function (Blueprint $table) {
   $table->id();
   .....

Rooms and Properties_Rooms

Schema::create('rooms', function (Blueprint $table) {
     $table->id();
     $table->string('name')->unique();
     $table->timestamps();
});

Schema::create('properties_room', function (Blueprint $table) {
     $table->id();
     $table->foreignId('properties_id')->constrained('properties')->onDelete('cascade');
     $table->foreignId('room_id')->constrained('rooms')->onDelete('cascade');
     $table->string('level');
     $table->string('note');
     $table->timestamps();
     $table->unique(['properties_id','room_id']);
});

Models

Rooms
public function properties()
{
     return $this->belongsToMany(Properties::class);
}

Properties
public function rooms()
{
     return $this->belongsToMany(Room::class);
}

The "Level" and "Note" fields seem correct to be in the lookup file. But i'm not sure if this is correct, or how to access them?

In my blade view, these all work:

• {{ $properties }} - Property Table Attributes
• {{ $properties->rooms }} - Room Table Attributes

But through the "Room" attributes, i'm not able to see the information held in the lookup file, which was obviously used?

Answer 1

Your privot table doesn't seem to be adding anything of value, so I would be inclide just to put your Room specific data on the Room table and create a relationship with a Property.

Property class

public function rooms()
{
  return $this->hasMany(Room::class);
}

Room class

public function property()
{
  return $this->belongsTo(Property::class);
}

Rooms table

Schema::create('rooms', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('floor');
    // etc ...
    $table->foreignId('property_id')->constrained();
    $table->timestamps();
});

Update

I might be misunderstanding your scenario, however, given the information provided I still think a pivot table is adding an unnecessary component/complexity. A Room does not belong to many Property, it exists in one Property. The pivot also adds an additional join which can be avoided by combining your tables.

Take the information about a Room from your pivot table and add it to your rooms table along with a foriegn key to the related Property (see above). You can add anything you want that describes a Room (e.g. floor area, if it has an en-suite, etc.). Granted you might get a few columns in your rooms table that only make sense for a particular Room type, however, a few empty fields is not going to make the world implode (that's only achievable when dividing by zero). Keep it simple, don't over complicate things.

A very basic example of how you might get a list of your properties and related rooms might look something like the following.

return view('properties', [
    'properties' => Property::with('rooms')->paginate(10)
]);

Then in your view you could loop over the properties and their rooms.

@foreach ($properties as $property)
    <h4>{{ $property->name }} has {{ $property->rooms->count() }} @choice('room|rooms', $property->rooms->count())</h4>

    @foreach ($property->rooms as $room)
        <p>{{ $room->type }} is located on the {{ $room->level }} floor</p>
    @endforeach
@endforeach