Title: Employee School Attendance in payroll system database schema
Tags: database;ms-access;database-design;relational-database;database-schema
Question: I have payroll system with attendance log-in and log-out, am and pm.
These are the tables:
DTR: list of all emloyees with there log-ins and log-outs for the day
Date    Empid    timein_am  timeout_pm  timein_pm  timeout_pm  status   tothrslate
12-4-13 1000     7:00       12:00       1:00       5:00        log-out  0
dtrlogs:
Columns are equal with the dtr tables. The attendance above will be moved here after the employee made an attendance for the day.
This table is list of all attendance of all employees everyday.
After the records of dtr table will be moved here, the time-ins and time-outs status, tothrslate will be deleted for the next day.
Questions:
Is it a good idea to have a dtrlogs in my database schema?
In any sense, is there anyway to do this for my attendance?
Btw, my client application is vb6 and I am using msaccess. Im just having a hard time designing database for payroll with attendance. I want to record all attendances of an employee everyday in a table. I was thinking to store attendances by months, meaning, I will have to create tables Jan-Dec. But I don't know if this is a good idea. Any advice? Any help would be apreciated.
Comment: I would never work here if I could only punch once in the am and once in the pm. I would totally work somehwere where I could punch *as many times as I wanted to*.

Comment: Sorry bout that but ive already updated my title. It is an school attendance for employee and based on the school that ive covered, there are only four time ins and outs.

Here is the accepted answer: The first thing I noticed was that your DTR table is very restrictive.  What if a boss decides that everyone needs to clock out when they take smoke breaks? With your DTR table, the employee can only clock in and out once in the afternoon.
You also need an Employee table with the employee's ID, name, phone number, emergency contact, etc.
I recommend 1 source table to hold your permanent data. It will never be deleted.  Give it 3 fields:
Clock_In
Clock_Out
This allows multiple 'work periods' during the day, including if someone skips lunch completely.  From this 1 table, you can schedule a nightly process that will clock out people who forget, build daily and monthly summaries, etc.
I also imagine this is a fairly common task.  Try googling it.
Comment for this answer: Hi again. Regarding your first comment, I have to stick to my suggestion.  What if the employee skips lunch? In that case, there'd only be 1 time-in and 1 time-out.  Also, is there an actual need to distinguish between morning hours and afternoon hours? If that's just for reporting purposes, then you can do that later in queries.
Comment for this answer: As for the next step, you'd need to create some queries to take your source table and summarize it.  The shape of these queries would depend on how you want to summarize the data. These queries could then dump the data into a temporary table from which you would build reports (this temp table is optional, but will probably run faster than executing the queries every single time).  If you don't know how you want to summarize the data, you might want to go back to the client and see what they want.
Comment for this answer: Also, someone down voted my response. Please explain? I'm listening.
Comment for this answer: Sorry bout that but ive already updated my title. It is an school attendance for employee and based on the school that ive covered, there are only four time ins and outs.
Comment for this answer: I understand your source table there, but I want to know what's next? Should I create another table for the dtr logs? If so, what should be the fields included considering I want to compute the total hours late for that day?
Comment for this answer: Hi. Thanks for your response. I'm a little bit clearer on what you are talking about. Since I am considering that, at the end of the month, I need to compute the days work, days absent. My plan is to put another table, which is the dtrlogs. I have a another question, instead of dtrlogs, would you prefer dtrlogs/month?I mean tables from january to december? What do you think?
Comment for this answer: let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/28116/discussion-between-handy-manny-and-poweruser)


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:04/11/13', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<database><ms-access><database-design><relational-database><database-schema>', 'Title': 'Employee School Attendance in payroll system database schema', 'ViewCount': 2214, 'id': 15958760, 'user_AboutMe': None, 'user_AccountId': 2482147.0, 'user_DisplayName': 'Handy Manny', 'user_Reputation': 388.0, 'user_Views': 55.0, 'user_id': 2161409.0}}