Employee attendance table in better way?

Question

_{(source: muchmore.url.ph)}

Here's it is how I want to design my SQL database table for employee attendance record.

Is this a correct way to do it?

As we can see it goes on increases date column row from 01 Jan 2014 to 31 Dec 2014 also next years too. so is there any other solution for this?

Answer 1

You could do it in the following way

EmployeeMaster
EmployeeId int primary key
EmployeeCode nvarchar(20)
EmployeeName nvarchar(50)

AttendanceDetails
AttendanceDetailsId int primary key
EmployeeId int foreign key references EmployeeMaster Employee Id
DateofDay datetime
PresentAbsent bit

SalaryDetails
SalaryDetailsId
EmployeeId int foreign key references EmployeeMaster Employee Id
Salary float

While selecting and showing you can play with the records with joins. you can pivot the records like Transpose in mathematics. There are plenty of ways to achieve it

Answer 2

Databases are designed to cope with large amounts of data. Don't worry about the number of rows in your table.

Make sure you can access all the data with efficient queries, this will be much better than fewer rows

Write some ideas down of how you want to access your data, e.g:

• Was EmployeeX in on Week2
• Recalculate the number of days in a month
• How many days off were sick days/holiday

Then work out what table structures you need to support this with simple queries

For the three above it becomes quite clear that your proposed table structure is going to make your SQL difficult.

Something more like EmployeeID | Date | Hours | Expected Hours | Absence Reason Might be more effective

Answer 3

Yes. Your table is in de-normalized form. This is not a correct way to design database. Try to normalize. Split this table into several tables with proper relationships. Search for normalization, learn how to do it. For example, You shouldn't keep Leave details and salary details along with attendance. Employee can be related with attendance, leave and salary tables by his ID while Employee details will be a separate Table which contains Employee's personal details.

You can do some thing like this:

EmployeeMaster{EmpID,FirstName,LastName,Address1,Address2,Address3,Address4,ContactNo,JoinnedDate}
SalaryMaster{EmpID, Basic, Variable, Allowance, Bonus, EPF,TAX}
LeaveMaster{ LeaveID,EmpID,StartDate,EndDate,Type,NoOfHalfDays)
Attendance { AttID, DateTimePunchedIn,DateTimePunchedOut}