Advice required for mysql simple mysql data design

[Ricky.]

Hi,

 

I have small need where I am supposed to take daily attendance record , say a company has 100 employees, now it want to record daily attendance record for all of them, I can create a table storing employe record , one for attendence and one for Holidays,

This issue is that only idea I get about employe attendance is as follow :

 

-------------------------------------------------

Date          |    employe ID |  Status

--------------------------------------------------

12-03-2012|    232            | 1

12-03-2012|    234            | 0

12-03-2012|    235            | 1

13-03-2012|    232            | 1

13-03-2012|    234            | 1

13-03-2012|    235            | 0

--------------------------------------------------

 

Here,  I will store date in Mysql Timestamp so that can do date based query on Mysql itself , here I have used three employe ID 232, 234, 235, and it is attendance for 2 days ie. 12-03-2012 and 13-03-2012 , where 0 and 1 is absent and present

 

So, if company has 100 employees, for a month it will go 30 x 100 = 3000 records for single month  and  for 6 months , it will then go 30 x 100 x 6 18000.

 

Is it the best approach I can do or something better is possible ?

 

 

 

[sunfighter]

What if you just recorded the absents? It would look like this:

 

employe ID |  absent

---------------|------------------

        234    |  12-03-2012

        235    |  13-03-2012

[Ricky.]

I also thought about that, however, since you also suggesting it , I will again give it a good look, however, I am only worried about creating reports , say I want to create  report of all 100 employees for particular period, will give me hard time to make it logically perfect.

[sunfighter]

Fill in the time BETWEEN for this query and you should have a report of all those that were absent during a time period:

 

select employe ID, count(employe ID)
  FROM table   // I'm not good with time but a BETWEEN start - stop date is needed here
  WHERE
  GROUP by employe ID
  HAVING count(employe ID) >= 1

 

[cpd]

Keep your logic inside the MySQL server and pull the data all at once for your reports. You can then manipulate it as you see fit in whatever language your using.

 

By keeping the logic in the MySQL server your application will run far more quickly as there is no hang time between the - for examples sake - Apache server and MySQL server.

[Ricky.]

What if you just recorded the absents? It would look like this:

 

employe ID |  absent

---------------|------------------

        234    |  12-03-2012

        235    |  13-03-2012

I had adopted this design and logically it was giving me headache on every step, I had to be real innovative to make things works other way, however, there were various situation like the employe who was always present in given time range.. I solved the issue but ultimately after deploying countless hours, I had to shift to the design I first proposed.. I did same task in 2 days for which it took me earlier 5 days (but yes with small db size).