Laravel time-punching database structure

Question

I am trying to build a database structure for a time punching system. What I'm trying to figure out is if what I have is the ideal structure, or if something else exists that I'm overlooking.

I want the records to be connected in some way so that I can pull out all of the records into a table and display them by date and time.

Here is my migration table:

Schema::create('punches', function(Blueprint $t)
        {
            $t->increments('id');
            $t->integer('user_id');
            $t->dateTime('time');
            $t->boolean('in_punch');
            $t->timestamps();
        });

I want to be able to have the in punch and the out punch connected, so I don't feel like this is the best way to go about doing it, but I can't come up with anything better. Can you?

Answer 1

I'm assuming what you want, ultimately, is show a list of all tuples (user, punch_in_time, punch_out_time). If so, the schema you showed does work, though the query would be ugly. One answer might be to have two tables:

ActivePunches(user_id, punch_in_time)

and

CompletedPunches(user_id, punch_in_time, punch_out_time)

When a user who isn't in the active list punches in, you add them to ActivePunches. When an active user punches out, you delete their active record and move it to completed. All other state transitions are illegal. You could also collapse these two into a single table (with an extra state column), but the nature of the problem indicates you might want to keep records for a long time, in which case you want to avoid reading a huge index, so this separation gives you a bit of a performance gain.

Answer 2

Under the assumption that every punch in will eventually have a punch out, I'd have one record per pair by adding the punch out time too:

$t->dateTime('punch_in_time');
$t->dateTime('punch_out_time')->nullable();

This prevents some errors that would otherwise be possible, such as only punching out. It also makes queries easier: everyone who's in right now is represented by a row with a null punch_out_time. You'll never have to worry about querying the wrong table either, which is messy and really complicates everything if you ever have another table with the foreign key punch_id.

Still, you'll need some validation elsewhere. Don't let a user create a new record if they already have one with a null punch out. Make sure the punch out time is after punch in, and their next punch in is after that.

---

You also need to add some integrity to the user_id:

$t->foreign('user_id')->references('id')->on('users')->onDelete('cascade');