Increase Database Performance By Using Multiple Columns

My Friend Sam Rowden and I were recently pairing on a project of his for a client, and came across an interesting requirement: serial numbers. The client wanted serial numbers to have the following format: [type]-[subtype]-[5 digit incrementing integer]. So, for example, "C-VB-00001", "C-VB-00002". The incrementing integers were to be unique the type, so there may well be "C-VB-00001" and "A-BC-00001" in the database at the same time.

Evidently, these serial numbers need to be generated programatically before inserting into the database. The most obvious solution here is a unique string column called serial_number on the table. On further reflection, however, there is a key problem with this approach: How do you retrieve the latest serial number on a per-type basis?

We could use some advanced SQL functions to achieve this, but as the table grows, this will have a detremental impact on performance. The fact of the matter is that we're going to need to get the latest entry every time we generate new serial numbers to work out the next number in the sequence, so this needs to be a fast query.

There is perhaps a much simpler solution that gives us all of the necessary power and speed.

Using multiple database columns for a single value

There are really two parts to these serial numbers: a type/subtype string and an incrementing integer. So, why don't we split the serial number into two parts in our database too?

We can have a string column serial_number_type and an int column serial_number_value. These two columns then form a unique index to prevent duplicate entries. Here's what that schema looks like:

$table->string('serial_number_type');
$table->unsignedInteger('serial_number_value');
$table->unique(['serial_number_type', 'serial_number_value']);

With that done, we need to combine these two columns programatically in our Eloquent Model. The best way to do this is using a custom Eloquent cast. Let's generate one using Artisan:

php artisan make:cast AsSerialNumber

The implementation of that cast might look something like this:

class AsSerialNumber implements CastsAttributes
{
 
    public function get($model, string $key, $value, array $attributes): string
    {
        return Str::of($attributes['serial_number_value'])
            ->padLeft(5, '0')
            ->start('-')
            ->prepend($attributes['serial_number_type'])
            ->__toString();
    }
 
 
    public function set($model, string $key, $value, array $attributes): array
    {
        return [
          'serial_number_type' => Str::beforeLast($value, '-'),
          'serial_number_value' => (int) Str::afterLast($value, '-'),
        ];
    }
 
}

Now, we can apply this cast to our model in the $casts array to a generated column called serial_number, like so:

class OurModel extends Model
{
 
    protected $casts = [
        'serial_number' => AsSerialNumber::class,
    ];
 
      // The rest of your model...
}

Anytime our model is retrieved from the database, it will now cast our serial_number_type and serial_number_value columns to a single attribute, serial_number, which is a complete representation of our serial number. Let's take a quick look at how our string manipulation works here:

Str::of($attributes['serial_number_value']) // 1, 2, 3 etc.
    ->padLeft(5, '0') // If the number is less than 5 digits, fill to the left with '0'
    ->prepend("{$attributes['serial_number_type']}-") // Add the type to the start
    ->__toString(); // Convert to a native PHP string

Casting back to the database is just a matter of splitting the serial number by the last '-' character. With this out of the way, we can now retrieve the latest serial number for a type using a very simple Eloquent query:

$latestSerialNumber = OurModel::select(['serial_number_type', 'serial_number_value'])
  ->where('serial_number_type', $type)
  ->latest('serial_number_value')
  ->first();

Incrementing the serial number is as simple as incrementing the returned serial_number_value by one.

Conclusion

So, the next time you're dealing with a situation like this in your applications, keep in mind that it may be easier to just split that value in your database and combine programatically!

Hopefully, you picked up some useful tips here. Be sure to follow me on Twitter for more!

Kind Regards, Luke