1

So i have to store daily attendance of employees of my organisation from my application . The part where I need some help is, the efficient way to store attendance data. After some research and brain storming I came up with some approaches . Could you point me out which one is the best and any unobvious ill effects of the mentioned approaches. The approaches are as follows

  1. Create a single table for whole organisation and store empid,date,presentstatus as a row for every employee everyday.
  2. Create a single table for whole organisation and store a single row for each day with a comma delimited string of empids which are absent. I will generate the string on my application.
  3. Create different tables for each department and follow the 1 method.

Please share your views and do mention any other good methods

  • What are you trying to do with this data. – Sign Oct 23 '12 at 18:24
  • All kinds of stuff like performance evaluation, salary calculation, work allocation etc. – Ali Abbas Oct 23 '12 at 18:31
7

I would create 3 tables: departments, employees, and absences.

CREATE TABLE departments ( id ... );

CREATE TABLE employees (
  id ...,
  department_id INTEGER REFERENCES departments(id),
  ...
);

Each employee would reference a department. If an employee was absent you would create an absence record in the database.

CREATE TABLE absences (
  ...
  employee_id INTEGER REFERENCES employee(id),
  absence_date DATETIME,
  ...
);

So, there would be a row in the absence table for each employee and the day he or she was absent.

You could leave out the departments table if that isn't important.

2

In short, to keep your database table as compact and as efficient as it can be, i would record ONLY days when employee was NOT present.

In addition, you may also have some flag on employee table to indicate that employee is not an active (was retired/fired), and skip logging his presence.

2

After seeing the OP comment about what he is going to do with these data, I thought this could be added to axblount's answer.

Have a dates lookup table: http://www.techrepublic.com/blog/datacenter/simplify-sql-server-2005-queries-with-a-dates-table/326

One query that will be difficult is to calculate the working days that occured in a given time span.

Use a Holiday table. The dates lookup table can be queried to find dates not in the Holiday table to find out how many days someone was expected to work. Paid holdays can be calculated as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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