Possibly setting up DB wrong?

rainman89

I cant find the any key..
Local time
Today, 02:02
Joined
Feb 12, 2007
Messages
3,015
Hi all,
I am trying to create a db for a timesheet program. i am running into issues on how to create the correct tables with the correct values.
i have 3 tables

Case
caseID (pk)
caseName
caseNumber
client

Task
taskID(pk)
task

Employee
employeeID(pk)
userName
userPass
employeeLast
employeeFirst

and one taht ties them together. This is where i get confused

timesheet
startTime
endTime
totalTime
notes
date (pk)
expenses
mileage

originally had employeeID, caseID and task ID in this but i wasnt sure that was right. so i removed them and added


employeeLast
employeeFirst
caseName
caseNumber
client
task

and linked them to the appropriate tables using the relationships....

I feel very strongly that this is the wrong design structure but am at a loss as to how else to design this..
i basically want the user to login using a login form i figured out from using this site... capturing the username to populate the employeeLast and First and then allowing the user to add as many client and tasks that they need to for that particular day.
thinking this would be done using a form and subform with employees and a timesheet subform..... is that the right track.? any help would be appreciated.:cool:
 
Consider a tTime table that looks more like this
Code:
[B]tTime[/B]
timeID (PK)
empID (FK)
caseID (FK)
taskID (FK)
start (DateTime)
end (DateTime)
notes
Seems to me that time, in a timesheet program, is connected to lots of other things.
Put expenses in an expenses table, since they'll be handled considerably differently.
As a rule, don't store totals in tables. It's a hidden dependency that all future objects will need to conform to, and you'll have to explicitly update fieldB whenever fieldA changes. Instead, use queries to summarize your raw data as required.
Cheers,
 
Hi lagbolt!

The reason i have total time in the timesheet table is because sometimes there is not a start time end time for a certain task(ie they dont remember when the started it, or they round up) so that is the reason for that.

So the proper way is to use the IDs in the time table rather than what i have stated before? I thought that was the way it should be done, but i did not know how to set up a form based on that, that will give me the options that i need. perhaps that is a question for the Form section of this forum.
 
Hi all,
I am trying to create a db for a timesheet program. i am running into issues on how to create the correct tables with the correct values.

Hi Rainman

When designing a database it is good to "talk through" the relationships.

One Employee is related to many Cases but a Case is only ever related to one Employee

One Employee is related to many Tasks but a Task is only ever related to one Employee

One Employee is related to many Timesheets but a Timesheet is only ever related to one Employee

Next you have to be really, really sure that these assertions are correct. Could two employees ever work together on a case? Could a task ever be splite betwen two Employees... etc.

Assuming the above was correct you would have a set of simple one-to-many relationships. The tables would then relate as follows:-

Case table has a foreign Key of EmployeeID.
Task table has a foreign key of EmployeeID
Timesheet table has a foreign key of EmployeeID

... BUT it is clear to me that the original assumptions are probably not correct. I'm pretty sure that you want to also track time by Case and Task? If that were correct the problem is easy but you will need another entity that you've not yet identified.

So do the exercise in the first paragraph first and talk me through the relationships, the schema design will fall out of that.

Your table and field naming conventions are nice too... just a few details that I'd change. You might find the standards published (free) on my site interesting.

Look forward to your "talk through" of the requirement.
 
Mike,
Thank you for your reply! Since my post i have progressed on my db and am seeing great results. Mostly through trial and error, but a great deal from reading other posts on this site (from people like you, willing to help) and searching the internet.
I probably should have closed this post... that is if i am able to do that. i thank you again for your help, and will keep your website for future use
 

Users who are viewing this thread

Back
Top Bottom