Pivot table to check user's availability

Question

I'm making a jobs website. Clients need to be able to search(checkbox input) the availability of contractors for certain days of the week.

I'm thinking about making a pivot table to store availability. ID column representing days of the week, Availability column showing true or false, and a user_id for the ID of the contractor.

I'm just not sure if it's the right way to do it. Wouldn't the user_id column have to store multiple user IDs? Can it be done?

Users migration:

Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password', 255);
        $table->string('address_address')->nullable();
        $table->double('address_latitude')->nullable();
        $table->double('address_longitude')->nullable();
        $table->rememberToken();
        $table->timestamps();
    });

user_availability table:

Schema::create('user_availability', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('Days');
        $table->foreign('user_id')
        ->references('id')
        ->on('users')
        ->onDelete('cascade');
    });

There are so many ways you can approach this. It is too subjective to really say what would be the best way. Think about what would be the easiest way to filter your data. — Software Person, Jun 26 '19 at 18:21
I'm think this is a many-to-many relation? I'm stuck at how to store the data. — Vincent Desrosiers, Jun 26 '19 at 18:28

score 0 · Accepted Answer · answered Jun 26 '19 at 19:02

Pick your poison:

As a integer with a bitwise search

// Constant values
$MONDAY = 0x01
$TUESDAY = 0x02
$WEDNESDAY = 0x04
$THURSDAY = 0x08
$FRIDAY = 0x10
$SATURDAY = 0x20
$SUNDAY = 0x40

// setting multiple days
$days = $MONDAY | $TUESDAY | $FRIDAY;

// check if a specific day is part of the days
if (($days & $Monday) == $Monday) {
   echo 'available on a Monday';
}

all days must be available:

SELECT * FROM user WHERE availability & :days;

some days must be available

SELECT * FROM user WHERE (availability | :days) > 0;

unavailable

SELECT * FROM user WHERE availability = 0;

A record per available day

Only one record per user

That should help me a lot, Thanks. – Vincent Desrosiers Jun 26 '19 at 19:14 — Vincent Desrosiers, Jun 26 '19 at 19:14

Pivot table to check user's availability

1 Answers1