I proceed to explain what the project that I just started is about to explain my concern.
It is a time tracking system but at its most basic level, nothing compared to the large systems that already exist (primaERP, Harvest etc) in which the client wants it to work as follows:
1-That in the system there are 3 types of users, the levels would be (admin, employee, client). Of course each user will have access to the system and will have own permissions depending on the type of user that is.
2-The admin will have all the privileges of the system, which I will describe the most important:
Creation of employees and creation of clients, who will have access to the system through e-mail and an established key.
Assignment of tasks to employees, in this part the admin will assign an employee a task in specific and a specific client, also must enter the time of duration of the task that can always be 2 weeks per task and with the peculiarity that they are 100 hours maximum per task.
Here my first question arises, which is how to save the last two data (cant weeks and maximum hours), what I think is to save both data in dry, 2 and 100 (if you can to do better, I would really appreciate the help)
Having the task already assigned to an employee, the user will enter the system and see what tasks are assigned to start with his weekly time tracking that will work as follows:
The employee enters the task assigned to start the day of work, entered one hour of entry, then lunch time, time of return to work and the time of departure from work. By having the 4 hours the system will calculate the total hours of work of the day and the employee will proceed to approve his work day belonging to the task assigned to him and corresponding to week 1.
Here I have the other question, in which way I can save the tracking of the day, I thought about keeping a row for each day with their respective 4 hours, but how would I know what week number these days belong to? work, if it is at 1 or 2
At the end of the week, the employee will approve his weekly time tracking, so that afterwards the client enters the system and proceeds to approve the employee's week.
Here is the other question, I think to handle this to approve the days and weeks with a field type bool, but my doubt is that if I approve my first week, as I do for when I have to approve the second
In itself, this last doubt goes more for the side where I can not think of how to know what days belong to such a week, if it is 1 or 2.
I will show the relational model that I have been doing so far, I appreciate a lot of help to be able to put it right, because I do not have many clear things.
Summing up my doubts are:
1-How to save the data of maximum hours and number of weeks.
Since for example with the field maximum hours, I must either subtract daily at 100 hours each time the employee approves a day. Or to go adding to that field every time the employee approves a day. And the field of number of weeks I do not know if I keep it dry from the one that arrives by POST, my doubt in this fact is that I do not know in what way I can use it to keep track of the days, to know to what week belong such days.
2-The 2nd doubt is what would be the best way to save the weekly tracking, if you keep saving day by day that I think is the way it should be done or there will be another better way.
3-And the last one is which is the best way to handle the approval data, both days and weeks. I think bool fields, I do not have so many doubts for the approval of the day, but if at the time of approving the week, because as I said will be 2 weeks of work, then in what way can I know when I approve week 1 and week 2.
I have given a lot of thought to the matter, but the logic is missing to put the tables together. And the main problem I think is in the weeks, how to know what week the 5 days of each user's time tracking belong.
I would be very helpful your guidance and advice to be able to put together the relational model well. Thanks.