Database Dictionary

There are some tables in the database. They are listed below:

Table 1. category -- list of possible categories of work for hours and jobs

Field NameData TypeField Definition
category_idtextthe id for the category
creator_idtextthe id for who created the category

Table 2. client -- list of all clients with open or closed jobs

Field NameData TypeField Definition
default_bill_ratemoneythe default billing rate for the client
client_idintclient id (generated externally from onShore TimeSheet)
default_approvedintif automatically approved, 0 = no, 1 = yes

Table 3. hours -- list of all logged, approved and deleted hours in onShore TimeSheet

Field NameData TypeField Definition
downloadedtextdownloaded because approved, 0 = no, 1 = yes
approval_datedatethe date the hours were approved
date_entereddatedate the user logged the hours, generated by onShore TimeSheet as the time the entry is made
time_intexttime the hours entry started
time_outtexttime the hours entry stopped
intimetextdate the user attributes the hours worked
expense_amountmoney 
fkapprover_idtextthe id of the supervisor who approved the hours
parkingrealparking fees accrued for this hours entry
commenttextcomments about the hours worked
hours_descriptiontextthe description of the hours worked
deltextis this a deleted hours entry, 0 = no, 1 = yes
billableintshould this be billable to the client, 0 = no, 1 = yes
hours_idintid of the hours being logged, generated internally by onShore TimeSheet
categorytextcategory for this work (from set list)

Table 4. job -- list of all possible jobs

Field NameData TypeField Definition
downloadedtextif the job has been downloaded or not, 0 = no, 1 = yes
default_approvedintany hours logged to the job are automatically approved
flag_hrsintnumber of hours logged to this job for flagging a warning to the supervisor
job_idintid of the job, generated internally by onShore TimeSheet
bill_ratemoneythe billing rate for this job
current_hours_estimatereal 
open_datedatedate the job was opened
estimated_hoursrealestimated number of hours for the completion of this job
openintjob is open or not, 0 = no, 1 = yes
close_datedatedate the job was closed
fknotify_idtextlist of user id's, as relate to the personnel_ids in the personnel table, to notify of the job's creation
current_estimate_datedatethe date the estimate of hours was made on the job
job_descriptiontextthe description of the job
categorytextcategory for this job (from set list)
fksupervisor_idtextthe id of the supervisor responsible for this job, as relates to the id in the personnel table

Table 5. notification -- table of who should be notified for actions on a job (not implemented)

Field NameData TypeField Definition
fkjob_idintthe job id number (relates to job_id in job table)
fkpersonnel_idtextthe personnel id number (relates to personnel_id in personnel table)

Table 6. personnel -- list of all personnel, employees and supervisors who use timesheet

Field NameData TypeField Definition
emailtexte-mail address
first_nametextuser's first name
personnel_idtextthe logon id for the user
last_nametextuser's last name
super_usertextif they have supervisor status or not, 0 = no, 1 = yes
middle_nametextuser's middle initial