Database Specification
The department has decided to build a database to handle tracking of personnel's professional development and percent effort. The design of the database and its tables is shown below.
Questions
- Where is the database? What type of database is it?
A
mySql database is used to store the data on the biostat server.
- Can I access the database?
Not directly; a front-end to the database is provided through PHP (see below for more details). You can access it
here.
- My VUNETID & password does not grant me access.
Authentication cannot be provided through Vanderbilt (at least for now). Contact
ColeBeck to acquire a password.
Table Design
Personnel Table |
Field |
Description |
Relationship |
vunetid |
primary key, assigned by VU |
->careerDev, professionalDev, %effort |
num |
employee ID, assigned by VU |
none |
firstname |
first name |
none |
lastname |
last name |
none |
middle |
middle initial/name |
none |
dob |
date of birth |
none |
social |
social security number |
none |
addr1 |
home address, line 1 |
none |
addr2 |
home address, line 2 |
none |
city |
city |
none |
state |
state |
none |
zip |
zip code |
none |
homephone |
home phone number |
none |
cellphone |
cell phone number |
none |
officephone |
office phone number |
none |
fax |
office fax number |
none |
email |
email address |
none |
rank |
degrees & certifications |
none |
department |
Vanderbilt department |
none |
location |
office location |
none |
note |
additional notes |
none |
modified |
date of last modification |
none |
|
Career Development Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
none |
vunetid |
VU's vunetid |
<-personnel |
pos |
position ID |
<-position |
begin |
start date of position held |
none |
end |
end date of position held |
none |
|
Professional Development Table |
Field |
Description |
Relationship |
number |
primary key, determined by form |
none |
vunetid |
VU's vunetid |
<-personnel |
date |
date of purchase |
none |
vendor |
vendor |
none |
payable |
payable to whom |
none |
description |
description of purchase |
none |
formtype |
form ID |
<-form |
amount |
amount of purchase |
none |
account |
account number |
none |
center |
center number |
none |
type |
category ID |
<-category |
|
Form List Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
->professionalDev |
formtype |
type of form, determined by form |
none |
|
Category List Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
->professionalDev |
category |
type of purchase |
none |
|
Position List Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
->career |
position |
full name of position |
none |
allocation |
yearly allocation to professional development |
none |
|
Project Table |
Field |
Description |
Relationship |
grantnum |
primary key, determined by grant |
->%effort, event |
head |
principal investigator |
none |
department |
department ID |
<-department |
contact |
contact's name |
none |
phone |
contact's phone number |
none |
title |
project title |
none |
agency |
funding agency ID |
<-agency |
center |
center number |
none |
shortname |
shorter, memorable project name |
none |
direct |
direct costs for life over project |
none |
indirect |
indirect costs over life of project |
none |
start |
start date of project |
none |
end |
end date of project |
none |
|
Department List Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
->project |
dept |
department name |
none |
|
Funding Agency List Table |
Field |
Description |
Relationship |
agencyID |
primary key, autonumber |
->project |
fundagency |
funding agency name |
none |
|
Event Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
none |
grantnum |
project number |
<-project |
status |
status of grant |
none |
priority |
priority score |
none |
percentile |
percentile score |
none |
date |
date of event |
none |
|
Percent Effort Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
none |
grantnum |
project number |
<-project |
vunetid |
VU's vunetid |
<-personnel |
percent |
personnel's percent effort |
none |
start |
start date |
none |
end |
end date |
none |
role |
role ID |
<-role |
|
Project Role List Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
->%effort |
role |
project role |
none |
|
Validation Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
none |
field |
field to validate |
none |
tbl |
table to validate |
none |
chartype |
character type |
none |
min |
minimum character length |
none |
max |
maximum character length |
none |
valmin |
minimum value |
none |
valmax |
maximum value |
none |
required |
required or not |
none |
|
User Name Table |
Field |
Description |
Relationship |
vunetid |
primary key, user name |
->session |
pass |
password |
none |
|
Session List Table |
Field |
Description |
Relationship |
id |
primary key, autonumber |
none |
vunetid |
username |
<-username |
created |
time stamp of session creation |
none |
modified |
time stamp of session modification |
none |
|
PHP Design
Initially, the PHP and HMTL were written as one. To increase maintainability the code has been separated into templates and classes. A normal user can only access information about themself and information about projects. Administrative users have the ability to add/edit/delete all information about personnel, purchases, projects, drop-down choices, and validation. The following shows the tables associated with each page.
- Personnel: Personnel, Percent Effort, Project, Project Role, Career Development, Position List, Professional Development, Form List, & Category List
- Projects: Project, Percent Effort, Personnel, Project Role, Event, Department List, Funding Agency List
- Purchases: Professional Development, Form List, Category List, & Personnel
- Drop-down Choices: Funding Agency List, Department List, & Project Role
- Validation: Validation