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
Topic revision: r2 - 29 Sep 2004, ColeBeck
 

This site is powered by FoswikiCopyright © 2013-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Vanderbilt Biostatistics Wiki? Send feedback