EverydayAttendance System SQL Problem

0 John Lester Mercado · September 8, 2015
I want to make a database that will save time in and time out for everyday attendance. I don't know how am I going you will associate the date to the time in and time out because I think mySQL doesn't have sub columns. PLEASE HELP.

Post a Reply


Oldest  Newest  Rating
0 Ron Butcher · September 11, 2015
I would do it by using foreign keys and multiple tables.  

Create a table called "users" that holds the user's information.  Create another table called "attendance_codes."  This table will basically have item 1 as "Check In" and item 2 as "Check Out."

Now you can make a third cable called "attendance_log."  This table will be where the check in/out is stored.  It should have four columns; "id," "user_id," "code_id," and "time_stamp."

When you update this table, the user id from the users table is entered, the code id from the attendance_codes table is entered, and the current time stamp is entered.  This makes the data easy to sort through when you are trying to search.  You can search by user id, checked in users, checked out users, or even by date and time.

Another cool part of this design is that the attendance codes can be expanded to include lunch in/out or break in/out as needed.

Here is a UML of what I described above with the column names.

0 John Lester Mercado · September 11, 2015
Thank you but if I update the table of check in check out. Will I be able to see it again or not? I want something that will save the data everyday.
0 Ron Butcher · September 12, 2015
Yes, as you update the attendance log it will keep every instance that was checked in and out.  Lets say you have two users:
     ID 01 - Ron
     ID 02 - Josh

And you have two attendance codes:
    ID 01 - Check In
    ID 02 - Check Out

As these users check in and out throughout the week your database will look like this:

01010109072015-08:00User Ron checked in at 8am on Sept 7th
02020209072015-08:03User Josh checked in at 8:03am on Sept 7th
03020209072015-17:00User Josh checked out at 5pm on Sept 7th
04010209072015-17:32User Ron checked out at 5:32pm on Sept 7th
05020109072015-09:12User Josh checked in at 9:12am on Sept 8th

and so on.....

This will make a separate entry for each check in and out event for as long as you keep the database running.  When you need to look back into your records you can filter your search by any one (or multiple) columns to get the data you need for a specific day, user, or action.
0 John Lester Mercado · September 13, 2015
I get it. Thank you. But I am having a problem in displaying the name with the log. because they are from the different tables. Can you give me the right sql code.
0 Ron Butcher · September 13, 2015
You will need to use a Join statement.  Here is an example:

SELECT `users`.`first_name`, `users`.`last_name`, `attencance_codes`.`code_description`, 'attendance_log`.`time_stamp` FROM `attendance_log` 
JOIN `users` ON `attendance_log`.`user_id` = `users`.`user_id`
JOIN `attencance_codes` ON `attendance_log`.`code_id` = `attencance_codes`.`code_id`
WHERE `users`.`first_name` = "Ron"

This selects everything that user Ron has done

first_name  |  last_name  |  code_description  | time_stamp
Ron         |  Butcher    |  Check In          |  09072015-08:00
Ron         |  Butcher    |  Check Out         |  09072015-17:32
0 John Lester Mercado · September 15, 2015
Thank you. I've done that. But my professor notice something. He doesn't want the name to be repeated. Ang he does want the time in and time out to be in separate column. Can we display a column that came from a row. This is what I want:

name    |    Date      |     Time_IN    |    Time_OUT 
Ron      |09072015  |   11:00           |   6:00
John     | (left blank)|    10:00           |  7:00   

Is there a way to do this?                
0 John Lester Mercado · September 17, 2015
I solved my last question using pivots. But can you help me to solve the absences and overtimes.
0 Ron Butcher · September 19, 2015
Can you give a little more information on what you are looking for with the absences and overtimes?  Are you trying to calculate daily work totals and note when an employee does not come in?

Edit:  never mind.  I just posted in your other topic.  Hope it helps.
  • 1

SQL & Databases


Everything SQL and Databases related in here!

Bucky Roberts Administrator