Laravel: Designing a DB table for schedules

  database, eloquent, laravel, mysql, php

I am creating an application and one of it’s features is going to be a person scheduler – you know, to create work schedules for staff members for the current & next months, where to write shifts, sick-leaves, day-offs, etc.

Right now I designed a structure using JSON format, like so:

enter image description here

Here, the schedule field contains a schedule for a corresponding month for the corresponding staff member in JSON format, like this: [{"date": "2020-10-01", "shift": "B"},{"date": "2020-10-02","shift": "B"}, ...].

This structure works and chose it because it won’t overload the DB with tons of entries for each day multiplied by the staff member quantity. But I realized that I won’t be able to search some fields, I will also need to calculate the Total working hours (and other hours) in my PHP AND JS as well, that means a lot of work and load for the server.

The second version is to make a table with just id (int), staff_id (int), date (date), value (string), where the shift (value) will be stored for each staff member for each day. But I may assume that it is going to be quite heavy after, let’s say, a year of usage with 10 staff members (at lease 3650 entries). But this will make it easier to search data, make statistic reports, make hour calculations (using only JS) and will simplify the server-side code.

The staff_id will contain an ID of the staff member and I will access that data using Eloquent relationship.

For you, as an expert, what version is better and why (speaking about application performance)? Won’t the DB entry quantity drastically affect it?

P.S. Let’s ignore the exporting features for now, but I understand that I will only be able to export this table, while only using version 2 of this.

P.P.S. I am using MySQL (for dev environment) and MariaDB (for staging and production).

Thank you!

Source: Laravel

Leave a Reply