Enum DB Columns, PHP Enums and Foreign Keys

In this lesson, we will discuss different kinds of Enums. There are two ways to use Enums: as a database column or with PHP native Enums.

Option 1: Database Column

First, let's look at the database enum column. You can create it by defining the enum() column in the migrations and providing the values as a second column in an array.

Schema::create('tickets', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->enum('status', ['open', 'closed'])->default('open'); 
    $table->timestamps();
});

But with database fields come some issues. First, where should these values be saved in the code? For example, when creating or editing a ticket, a user might need to be able to select a status. You may need to filter by ticket status somewhere.

One way to deal with this issue could be to define statuses as constants in the model.

class Ticket extends Model
{
    protected $fillable = [
        'title',
        'status',
    ];
 
    public const STATUS = [ 
        'Open',
        'Closed',
    ]; 
}

Then, when showing the status for the select input, you can do a foreach loop for this constant.

<select name="status" id="status" required>
    <option disabled hidden {{ old('status') != null ?: 'selected' }}>
        Select status
    </option>
    @foreach(\App\Models\Ticket::STATUS as $status)
        <option value="{{ $status }}" {{ old('status') != $status ?: 'selected' }}>
            {{ $status }}
        </option>
    @endforeach
</select>

But the values are only in the code. Here comes the second issue at the database level. What if you need to add a new status for the tickets later, like In Progress?

Adding a new value to the constant isn't enough; you must also add it to the database. And Enum is in every record. So, adding a new enum value adds it to every record, which could cause problems.

Option 2: Native PHP Enums

Since PHP 8.1, you can use the enum class. Laravel also has native support for PHP enums. Laravel 11 also added an artisan command to create an enum class. For example, the same statuses for the ticket can be:

php artisan make:enum Enums/TicketStatus --string

This command generates the TicketStatus enum class in the app/Enums folder and backs it as a string.

app/Enums/TicketStatus.php:

enum TicketStatus: string
{
    case OPEN = 'open';
    case CLOSED = 'closed';
}

The field in the database can then be a varchar or integer. The default value can also be set from the enum class.

use App\Enums\TicketStatus;
 
Schema::create('tickets', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->string('status')->default(TicketStatus::OPEN);
    $table->timestamps();
});

The most important part when using PHP enums is casting the field to the enum class in the Model.

use App\Enums\TicketStatus;
 
class Ticket extends Model
{
    protected $fillable = [
        'title',
        'status',
    ];
 
    protected function casts(): array
    {
        return [
            'status' => TicketStatus::class, 
        ];
    }
}

Now, you can use other features that Laravel provides, like validation. Adding a new status would only require adding a new value to the enum class.

You can read more about PHP enums in the PHP 8.1: Enums article by Brent.

Performance: Enum vs Tinyint Foreign Key

In the above case, we save the enum as a varchar type. But what about its performance compared to a foreign tinyint field? I recently performed an experiment. In the database, I seeded 50k tickets.

Running the benchmark ten times gives a little more than 6ms.

Benchmark::dd([
    fn () => Ticket::where('status', 'open')->count()
], 10);

array:1 [▼ // vendor/laravel/framework/src/Illuminate/Support/Benchmark.php:67
  0 => "6.423ms"
]

Let's create a new table for statuses.

By the way, did you know about the ->tinyIncrements() method? It creates a TINYINT column as an auto-incremented primary key instead of a default BIGINT generated by $table->id().

use App\Models\TinyStatus;
 
return new class extends Migration {
    public function up(): void
    {
        Schema::create('tiny_statuses', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
 
        TinyStatus::create(['name' => 'open']);
        TinyStatus::create(['name' => 'closed']);
    }
};

Next, let's add a tinyint foreign key to the tickets table and update it in the same migrations.

public function up(): void
{
    Schema::table('tickets', function (Blueprint $table) {
        $table->unsignedTinyInteger('tiny_status_id')->default(1);
        $table->foreign('tiny_status_id')->references('id')->on('tiny_statuses');
    });
 
    $sqlStatus = "UPDATE tickets set tiny_status_id =
    case when status='open' then 1
        else 2
    end";
    \Illuminate\Support\Facades\DB::statement($sqlStatus);
}

Let's add a second benchmark.

Benchmark::dd([
    fn () => Ticket::where('status', 'open')->count(),
    fn () => Ticket::where('tiny_status_id', 1)->count(),
], 10);

And the result now is:

array:2 [▼ // vendor/laravel/framework/src/Illuminate/Support/Benchmark.php:67
  0 => "8.552ms"
  1 => "3.319ms"
]

So, tinyint is more than 2x faster. But wait! Status doesn't have indexes added.

Schema::table('tickets', function (Blueprint $table) {
    $table->index('status');
});

Let's rerun the benchmark.

array:2 [▼ // vendor/laravel/framework/src/Illuminate/Support/Benchmark.php:67
  0 => "6.837ms"
  1 => "3.811ms"
]

In this case, the index on the status field improves performance, but tinyint is still 2x faster.

Conclusion

If you're choosing between an Enum column and a separate DB table, consider how often you would need to add new values to that table.

If it's close to "almost never", then Enum is an option. Otherwise, having a separate DB lookup table with values is almost always more beneficial.