1
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ @Mast I updated my question. Sorry I posted this kinda late and was pretty tired \$\endgroup\$ – watzon Apr 30 '15 at 14:52
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Would you mind showing me an example of the schema you'd use for each database table? \$\endgroup\$ – watzon May 3 '15 at 17:10
  • \$\begingroup\$ The two relations above really are the schema. Just add the auto-increment ids to each, if you need them. \$\endgroup\$ – miki May 4 '15 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.