I am trying to developing a projects/employees/hours application and need a database design. I haven't done it for a while so I am seeking good advises.
Basically, the application is to track how many hours the employees have worked on the projects. The employees have to log in to the system everyday to put the hours they spend on the projects they work on. By the end of the week, they submit the timesheet and show the total hours they have worked on every projects.
For example, employee A works 3 hours on project A on 2/11/12 and employee B works 2 hours on project A and 1 hour on project B on 2/9/12. The manager can see the the 2 employees have spend 5 hours on project A and 1 hour on project B when they log in. The manage can also see how many hours the employee B has spend on all the projects (A and B). The database tables I have in mind are
EMPLOYEES
id/name/password/manager_privi
PROJECTS
id/project_name/client/hours/date
HOURS
employee_id/project_id/date
Is this enough for what I need? Thanks for the help.
I'm a bit confused about the hours and date columns you assigned in the question, so I'll be using more specific names. This is how I see it.
PK is between quotes
CLIENTS("id", name)
It was referenced and was missing :)
EMPLOYEES("id", name, password, manager_privi)
Not sure what manager_privi is... may it be the employee's manager?
PROJECTS("id", project_name, client_id, start_date, end_date)
Splitted the date in two in order not to mix with the worked date
HOURS("employee_id, project_id, worked_date", hours)
Note it is a compound key. You might also have to create indexes on project_id and worked_date to speed up things, but that's not related to it's design Note that an employee can not have 2 records for the same date to the same project. In this case you'll have to UPDATE the hours amount if the employee tries to add more hours for same date and project
Hope this helps.