How to define database tables?

Question

I need to compute X hours worked for an X employee in month X. For example, John worked 20 days in January, 12 in February, and so on. To lost in relation to table, create a table only for dates? Or create columns within the employee table with: jan, fev, mar. In which case I don’t think it’s the right thing to do.

Answer 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 level of accuracy of the report, it’s worth putting each date per line - keeping all hours of a month in the same line will make it more complicated to recalculate some day that has been recorded wrong on the time 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 02/01, run

SELECT * FROM HORAS_TRABALHADAS WHERE funcionario_id = 1 AND data = 02/01/2014

Answer 2

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

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

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

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

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

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

Before saving the data, re-convert the array to string. To do this use the function implode:

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

This is an example if you only need to record the number of days.


If you want to record the date the work was done, you need to create a table by part.
Create a new table, for example "TB_HORAS" with the fields:

• ID - int //autocomplete
• ID_FUNCIONARIO - int //id of the respective employee corresponding to the employee table
• DATA - date //date on which the work 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'

Answer 3

Record 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 employees and another for daily logging 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) See 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.

Answer 4

I thought a little and I think what you can do is this:

• Create the fields NAME, DIAS_TRABALHADOS, MES (if necessary create YEAR)

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