How to define the database tables?

0

I need to compute X hours worked for an employee X in month X. For example, John worked 20 days in January, 12 days in February, and so on. I'm lost in relation to the table, do I create a table only for dates? Or create columns inside the employee's table with: jan , fev , mar . Which in the case I think is not the right thing to do.

    
asked by anonymous 15.05.2014 / 17:06

4 answers

1

I believe this solution will help you:

Tabela FUNCIONARIOS =>

functionario_id  nome
1                João
2                André


Tabela HORAS_TRABALHADAS = >

funcionario_id  mes  horas
1               10   20
2               10   12
1               11   5
If you want to increase the accuracy level of the report, it pays to put each date by line - keeping all the hours of a month on a same line will make it more complicated to recalculate some day that has been recorded wrong on the point sheet .

Tabela HORAS_TRABALHADAS = >

funcionario_id  data        horas
1               02/01/2014  8
2               02/01/2014  8
1               02/01/2014  5

So if you want to select John's line on 01/01, run

SELECT * FROM HORAS_TRABALHADAS WHERE funcionario_id = 1 AND data = 02/01/2014
    
15.05.2014 / 17:22
1

If you just want to count the number of days , simply create a field. When generating reports, avoid adding one more table to your query.
Within this field write the data as follows:

12,15,13,5,0,0,0,0,0,0,0,0

Each position represents a month, January, February, etc. ...
To add +1 day to the respective month, before recording, you have to convert the data to array using the explode function:

$aDias = explode(",",string_com_os_dias);

In the string that we use as an example, we have until the month of April completed. An example to add another day to the month of April.

$aDias[3] += 1; //passa a 6

Before writing the data, convert the array back to string. To do this use the implode function:

$sDias = implode(",",$aDias);

This is an example if you only need to save the number of days. If you want to save the date the work was done, you need to create a table to part.
Create a new table, for example "TB_HORAS" with the fields:

  • ID - int // autocomplete
  • FUNCTIONAL ID - int / id of the corresponding employee corresponding to the employee table
  • DATE - date // date the job was performed
  • HOURS - int // number of hours

To search by month and count every day, for example, month of May for employee with ID = 5:

SELECT SUM(HORAS) FROM TB_HORAS WHERE ID_FUNCIONARIO = 5 AND DATA between '2014-05-01' AND '2014-05-31'
    
15.05.2014 / 17:27
1

Record the records per day. This will give you more options for improvements to your system in the future and will allow you to have years history.

1) Create a table for officials and another for daily record of hours worked:

funcionario
--------------
id    nome
1     João
2     José
3     Maria

horas_trabalhadas
--------------
id    funcionario_id   data           horas
1     1                2014-05-02     8
2     1                2014-05-03     6
3     2                2014-05-02     7

2) View the hours worked by each employee in a month (in the example below May 2014):

SELECT f.id. f.nome, sum(ht.horas) 
FROM funcionario AS f
JOIN horas_trabalhadas AS ht
     ON f.id = ht.funcionario_id
WHERE DATE_FORMAT(ht.data, "%Y") = 2014 AND DATE_FORMAT(ht.data, "%m") = 5
GROUP by f.id, DATE_FORMAT(ht.data, "%Y-%m")
ORDER BY f.nome;

If you want a specific user just add the condition "f.id = {employee ID}" to the query.

    
16.05.2014 / 04:59
0

I thought about it a little and I think what you can do is the following:

  • Create the fields NAME, TRACKED DAYS, MONTH (if necessary, create YEAR)

There you insert the name of the guy, how many days he worked and in which month he worked. This saves you from getting too much data in the INSERT. And it would help you in SELECT, because you would only have to pass a WHERE = month that you want to consult.

    
15.05.2014 / 17:15