SQL Database Design – Storing Attendance Data Efficiently

database-designsqlsql serverstorage

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

Best Answer

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.

Related Topic