Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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