====== Database Tables ======

This is a listing of the database tables and fields used in the design. The description explains any special interpretation needed for each field.

During the installation, the administrator can select the prefix used for the table names in the database, by default this is "timesheet_".
===== Core =====
==== Client ====
^ Field ^ Type ^ Description |
|client_id |int(8) |Primary key. |
|organisation |varchar(64) |This is used as the title. |
|description |varchar(255) | |
| . . . | |A whole bunch of fields that are only interesting from a CRM point-of-view. |

==== Project ====
^ Field ^ Type ^ Description |
|proj_id |int(11) |Primary key. |
|title |varchar(200) | |
|client_id |int(11) |Reference to the client |
|. . . | | |

==== Task ====
^ Field ^ Type ^ Description |
|task_id |int(11) |Primary key. |
|proj_id |int(11) |Reference to the project |
|. . . | | |

==== Assignments ====
This is the user to project assignments.

^ Field ^ Type ^ Description |
|proj_id |int(11) | |
|username |char(32) |**Should be uid not username?** |
|rate_id  |int(11) |Each user on a project has a billing rate defined, which can be different for different projects.|

==== Task_Assignments ====
^ Field ^ Type ^ Description |
|task_id |int(8) | |
|username |varchar(32) |**Should be uid not username?** |
|proj_id |int(11) |Why is this here too? Is it used/needed at all? |

==== Times ====
^ Field ^ Type ^ Description |
|uid |varchar(32) |User name. |
|start_time |datetime | |
|end_time |datetime | |
|trans_num |int(11) |Primary key. |
|proj_id |int(11) |Why is this here too? Is it used/needed at all? |
|task_id |int(11) | |
|log_message |text |Free comment field. |

===== Set-up =====
==== Billrate ====
^ Field ^ Type ^ Description |
|rate_id |int(8) |Primary key. |
|bill_rate |decimal(8,2) |Dollars per hour. |

==== Config ====
^ Field ^ Type ^ Description |
| config_set_id | int(1) | NOT NULL default '0', Primary Key |
| version | varchar(32) | NOT NULL default '_TIMESHEET_VERSION_',| 
| headerhtml | mediumtext | NOT NULL,| 
| bodyhtml | mediumtext | NOT NULL,| 
| footerhtml | mediumtext | NOT NULL,| 
| errorhtml | mediumtext | NOT NULL,| 
| bannerhtml | mediumtext | NOT NULL,| 
| tablehtml | mediumtext | NOT NULL,| 
| locale | varchar(127) | default NULL,| 
| timezone | varchar(127) | default NULL,| 
| timeformat | enum('12','24') | NOT NULL default '12',| 
| weekstartday | TINYINT | NOT NULL default 0,| 
| useLDAP | tinyint(4) | NOT NULL default '0',| 
| LDAPScheme | varchar(32) | default NULL,| 
| LDAPHost | varchar(255) | default NULL,| 
| LDAPPort | int(11) | default NULL,| 
| LDAPBaseDN | varchar(255) | default NULL,| 
| LDAPUsernameAttribute | varchar(255) | default NULL,| 
| LDAPSearchScope | enum('base','sub','one') | NOT NULL default 'base',| 
| LDAPFilter | varchar(255) | default NULL,| 
| LDAPProtocolVersion | varchar(255) | default '3',| 
| LDAPBindUsername | varchar(255) | |
| LDAPBindPassword | varchar(255) | |
| LDAPBindByUser | tinyint(4) | NOT NULL default '0',| 
| LDAPReferrals | tinyint(4) | default 0,| 
| LDAPFallback | tinyint(4) | default 0,| 
| aclStopwatch | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclDaily | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclWeekly | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclCalendar | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclSimple | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclClients | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclProjects | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclTasks | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclReports | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclRates | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| aclAbsences | enum('Admin','Mgr','Basic','None') | NOT NULL default 'Basic',| 
| simpleTimesheetLayout | enum('small work description field', 'big work description field', 'no work description field') | NOT NULL DEFAULT 'small work description field',| 
| startPage | enum('stopwatch', 'daily', 'weekly', 'calendar', 'simple') | NOT NULL DEFAULT 'calendar',| 

==== Users ====
^ Field ^ Type ^ Description |
|username |varchar(32) |Login name. |
|level |int(11) |Role-based access-control level. 0 is basic, 5 is manager, 10 is administrator. |
|password |varchar(41) | |
|first_name |varchar(64) | |
|last_name |varchar(64) | |
|email_address |varchar(63) |Don't ask why it is defined with a length 63. It is not used anywhere. |
|time_stamp |timestamp |Time of last update to entry. |
|status|enum('IN', 'OUT') |Not sure whether this is used. |
|uid |int(11) |Primary key. |

===== Attendance =====
==== Absences  ====
^ Field ^ Type ^ Description |
|entry_id |int(6) |Primary key. |
|date |datetime | |
|AM_PM |enum('day','AM','PM') |Morning, afternoon or the whole day. //This doesn't cover the "go home 1 hour early before a public holiday" case//|
|subject |varchar(127) |Descriptive title. |
|type |enum() |Defined types of absences: 'Holiday' - personal vacation, 'Sick' - Illnesses, 'Military' - Don't joke, 'Training' - 'Compensation' - Glidedays (or half), 'Other' - Company-wide, or user special absences (Bridge-days, weddings, etc.), 'Public' - Defined public holidays. |
|user |varchar(32)|When null (''), indicates an absence that applies to all users - public, other. |

==== Allowances ====
^ Field ^ Type ^ Description |
|entry_id |int(11) |Primary key. |
|username |varchar(32) | |
|date |date |Most dates have type 'datetime', this doesn't. |
|holiday |int(11) |This should probably be a real number or a 'time' instead of an integer. |
|glidetime |time | |

The idea is that user 'joe' gets an allowance of 200 hours holiday on 1-Jan-2008, then again another 200 on 1-Jan-2009. Glidetime "allowance" would be negative, ie. 'joe' had 150 hours paid out (in salary) on 1-Jun-2008, therefore he gets -150 on this date to his glidetime balance.

The earliest dated entry for a user is used internally to indicate his/her start date in a company when calculating balances.

====== Comments ======
  - The choice of datatypes is fairly inconsistent, lots of different sized integers and strings are used.
  - No clear naming convention is used.
  - The user ID field is unique but not used much by the other tables (if at all). The login string name 'username' is used. There is some PHP code to handle when a username is changed but it is weak.