current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite

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?

share|improve this question
1  
@Mast I updated my question. Sorry I posted this kinda late and was pretty tired – sun-tzu Apr 30 '15 at 14:52
up vote 2 down vote

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.

share|improve this answer
    
Would you mind showing me an example of the schema you'd use for each database table? – sun-tzu May 3 '15 at 17:10
    
The two relations above really are the schema. Just add the auto-increment ids to each, if you need them. – miki May 4 '15 at 13:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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