You are here:
Vanderbilt Biostatistics Wiki
>
Main Web
>
DeptOps
>
DeptComputing
>
ProDevGrantDBProject
(29 Sep 2004,
ColeBeck
)
(raw view)
E
dit
A
ttach
---++ 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 [[http://www.mysql.com/][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 [[http://biostat.mc.vanderbilt.edu/dbconnect/index.php][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 <!-- HTML is embedded on this page in order to increase the amount of information displayed on the screen --> <table><tr><td width="50%"> | *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| </td><td> | *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| </td></tr></table><br> <table><tr><td width="50%"> | *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| </td><td> | *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| </td></tr></table><br> <table><tr><td width="50%"> | *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| </td><td> | *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| </td></tr></table> ---+++ 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
E
dit
|
A
ttach
|
P
rint version
|
H
istory
: r2
<
r1
|
B
acklinks
|
V
iew topic
|
Edit
w
iki text
|
M
ore topic actions
Topic revision: r2 - 29 Sep 2004,
ColeBeck
Main
Department Home Page
Biostatistics Graduate Program
Vanderbilt University Medical Center
Main Web
Main Web Home
Search
Recent Changes
Changes
Topic list
Biostatistics Webs
Archive
Main
Sandbox
System
Register
|
Log In
Copyright © 2013-2022 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