employee timecard

[uglyeyes]

Hi I am in process of designing a online employee timesheet application which allows employee to record hours and manager to approve number of hours worked. could someone please point me to links which gives me idea on how to create tables i.e. employee, timecard, etc

[leckey 0]

Hi uglyeyes-- You have been a member long enough that CodeProject is not here to answer generic questions such as these. Your question is one where most would probably tell you to "just Google it." Creating a basic database and its tables is part of the learning process. Sit down, take some time, and think it out.

Back in the blog beatch! http://CraptasticNation.blogspot.com/[^]

[dan sh]

What exactly is your question? How to create table called employee, timecard etc in some database? or You are looking for guidance related to database design?

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

[i i i]

Which Tools are you using to do this , you have not explain that you are going to use Sql Server or MySql , for online System i will recommend MySql any how here is the link for Ms Sql Server http://databases.about.com/od/sqlserver/ss/sqlservertables.htm[^]

Best Of Regards, SOFTDEV If you have knowledge, let others light their candles at it

[Lost User]

u can create this sample tables CREATE TABLE [dbo].[ATTENDANCE]( [AttendanceID] [int] IDENTITY(1,1) NOT NULL, [EmployeeID] [int] NULL, [AttendanceCreatedDate] [datetime] NULL, [AttendanceCreatedByUserID] [int] NULL, [AttendanceDate] [datetime] NULL, [InTime] [datetime] NULL, [OutTime] [datetime] NULL, [WorkDuration] [int] NULL, [IsOverTime] [bit] NULL CONSTRAINT [DF_Attendance_IsOverTime] DEFAULT ((0)), [AttendanceComments] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Attendance] PRIMARY KEY CLUSTERED ( [AttendanceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UK_Attendance] UNIQUE NONCLUSTERED ( [EmployeeID] ASC, [AttendanceDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Thanks and Regards Sujit Kr. Mandal

[uglyeyes]

Hi Guys, thanks for your response. I should have been more specific. I have already created few tables which are as below. Employee, EmployeeType, Jobtitle, Period (PeriodId PeriodStartDate PeriodEndDate), timeCard(TimeCardId,PeriodId,EmployeeId,Date,StartTimeAM,EndTimeAM,StartTimePM,EndTimePM,BreakTaken,UpdatedDate,IsApproved) period 17 2009-09-21 00:00:00 2009-09-27 00:00:00 timecard for one week for one employee 1 17 1 2009-09-21 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0 2 17 1 2009-09-22 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0 3 17 1 2009-09-23 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0 4 17 1 2009-09-24 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0 5 17 1 2009-09-25 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0 6 17 1 2009-09-26 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0 7 17 1 2009-09-27 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0 for you to understand I have provided columns of period and timecard. this web application is used by hospitality industry. employee works shifts like 7am-12pm and 6pm-2am Now, I am thinking I shouldnt store computed values. My problem is how do i work it out below: If they work after mid-night they should get penalty and those two hours after midnight should be moved to record also I dont know how to calculate their pay. could you please help?

[uglyeyes]

Ok, to simply it. how can I compare the time value as an input. my idea is if they type endtime anything bigger than 12.00am I need to compare mid-night time and @endtime, if @endtime is after midnight I need to add to append the hours next day in next row. declare @starttime smalldatetime declare @endtime smalldatetime set @starttime='18:20' set @endtime ='2:00' print DATEADD(hh, DATEPART(hour,@EndTime), DATEADD(mi,DATEPART(mi,@EndTime),DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) ) print DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0) Hope that makes sense? thank you

modified on Monday, October 5, 2009 8:45 PM

[David Mujica]

To make your design more flexible, you need to implement a parent / child relationship where the master is the Timecard and the child records represent slices of time within that timecard. Each time slice can have attributes like Paid/Unpaid, Payrate, Workcode (Work, Absence, Break), Absence code, start time, end time. For example: WORK 7:00 - 9:00 BREAK 9:00 - 9:15 WORK 9:15 - 12:00 BREAK 12:00 - 13:00 WORK 13:00 - 17:00 Consider going to CodePlex to see what other people have developed, there may be a solution already out there. :cool:

[uglyeyes]

Hi, thanks I went to codeplex but I couldnt find the solution i was looking for. could you please give me an idea how the table relationship might look like between timecard and time slices. thanks once again

[David Mujica]

Here is a rough idea of how you could design a time keeping database ... Table: Timecard_Header HID - Primary key Auto generated sequence number. (Header ID) EMPID - Key to the employee table TCDATE - Timecard date TIME_IN - Datetimestamp of when the timecard begins (shift start) Include the date portion incase the shift begins at 11pm and runs across midnight TIME_OUT - Datetimestamp Table: Timecard_Details DID - Primary key. Sequence number (detail ID) HID - Foreign key reference back to the header record TIME_START - Beginning time for this time slice TIME_END - Ending time for this time slice TDTYPE - Timecard deail type (Use this to indicate Work, Break, Absence, etc) TDCODE - Used as a further explaination of the type, for example Absence - Vacation or Absence - Jury duty PCODE - Paycode (0 - Unpaid, 1 - Regular pay, 2 - Overtime, 3 - Doubletime, etc) PRate - Pay rate show the actual rate paid for this time slice. $10/hr Regular pay or $15 / hr for overtime This should give you an idea of one way of looking at the data, you should extend this model to meet your requirements.