-2

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user:

  • To enter for each day of the week the amount of time he worked for a specific project.
  • two phases of work per day ===> meaning employee can work on two seperate projects in one day
  • when doing reports(employee regular hours/mounth) how to deal with weekends holidays and vacations
Improve this question
3
  • 3
    "I'm designing the database schema for a time tracking application and I need a little piece of advice. " -- edit your question with what you have designed so far, we're not going to do this for you
    – Philᵀᴹ
    Commented Aug 18, 2018 at 12:58
  • "two separate projects" sounds like an antipattern. It is often a sign of bad strategy to specify a constraint involving a number other than 0, 1, or ∞. See the Zero One Infinity Rule. Columns like project_id_1 and project_id_2 would be the big red flag, here.
    – Michael - sqlbot
    Commented Aug 18, 2018 at 23:43
  • employees in this company work two shifts in one day(08-12 and 14-18). so one employee can work for exemple only the moring in one day or even two seperate projects the next day. how do you suggest to handle this?
    – Zakaria Naji
    Commented Aug 19, 2018 at 14:38

1 Answer 1

Reset to default
0

From what I understand, the doubt is about the treatment of weekends and holidays.

I suggest creating the following structure: A table to store information and holidays, owning day, month, holiday name and holiday type. You should have a table of parameters, to inform if you want to postpone weekend When to make your query on the working days worked, disregarding the days of weekends and the days of the registered holidays.

I have already made a similar structure to what you have reported and the way I have explained will work perfectly.

Improve this answer
2
  • thank you Germano Manente Neto, i think your solution will do it
    – Zakaria Naji
    Commented Aug 18, 2018 at 14:21
  • I'll help you whatever it takes.
    – Germano Manente Neto
    Commented Aug 20, 2018 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.