Med College policies db

I’ve been working with my dean to begin reorganizing our College of Medicine Student Handbook content from one comprehensive (cumbersome) pdf document into a searchable website very similar to the Animal Science Majors Student Handbook (http://asci.uvm.edu/current/handbook/). We absolutely LOVE the way the main campus Compliance Office has organized the University’s policy website (http://www.uvm.edu/policies/?Page=alphalist.php)and plan to link to it under the ‘Policy Section’ of our forthcoming “document.” However, COM also has some policies unique to our program that we’d like to organize and present much like on the University’s parent site.

Erica Heffner suggested that I connect with you, as the guru who helped them organize the policies in an SQL database and assisted with programming the interface. I’m pretty adept at databases, but have never created one specifically for a website. I was hoping you might be willing to give me a glimpse at how you went about this for the main UVM policy page, so that I don’t struggle trying to re-invent a wheel.

I’m also curious about recommended practices around “versioning” and welcome your thoughts. Erica explained that her office doesn’t use an elaborate database or system; but rather when they replace a policy, they create a new version number, keep the retired policy in an archived folder with the date the policy was replaced, and that the new version of the policy includes an effective date.

Is there any chance you’d be willing to share some insight/advice with me as I prepare to embark on this undertaking? Or someone else you might refer me to, if not?

Entered on 09/19/2013 at 13:55:17 EDT (GMT-0400) by Wesley Wright:

The Policies database is a php application on www.uvm.edu interfaced with a MySQL database living on webdb.uvm.edu

You can get an account on www.uvm.edu here:

http://www.uvm.edu/it/account/?Page=apply-department.html

And you can get a mySQL database here:

https://webdb.uvm.edu/

Polices db is pretty simple. Main table looks like this:

CREATE TABLE `policy_docs` (
`doc_id` varchar(100) NOT NULL DEFAULT ”,
`filename` varchar(100) DEFAULT NULL,
`title2` varchar(200) DEFAULT NULL,
`category` varchar(255) DEFAULT NULL,
`official` varchar(255) DEFAULT NULL,
`review_year` char(10) DEFAULT NULL,
`status` char(2) DEFAULT NULL,
`board` char(2) DEFAULT NULL,
PRIMARY KEY (`doc_id`)
)

doc_id is compliance internal numbering scheme (http://www.uvm.edu/policies/?Page=numberingsystem.html&SM=SubProcessMenu.html)

filename is name/location of PDF

title2 is full document title

category is comma delimited list of applicable category codes, related to table categories

official is code for responsible office/officer, see table officials

review_year is sort of a last reviewed/next review date

status is code letter, f for final, d for draft

borad is Governance code b Board of Trustees Institutional

Other tables:

# Dump of table categories
# ————————————————————CREATE TABLE `categories` (
`code` char(2) NOT NULL DEFAULT ”,
`cat_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Dump of table community
# ————————————————————

CREATE TABLE `community` (
`comm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`doc_id` varchar(100) NOT NULL DEFAULT ”,
`community` varchar(100) NOT NULL DEFAULT ”,
PRIMARY KEY (`comm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1;

# Dump of table officials
# ————————————————————

CREATE TABLE `officials` (
`code` char(4) NOT NULL DEFAULT ”,
`official` varchar(255) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Typical SQL:

SELECT policy_docs.doc_id, policy_docs.title2,policy_docs.filename, policy_docs.category, board from policy_docs where status=’f’ order by title2

Don’t know much about versioning. You could come up with a version numbering scheme, put a code in status indicating “Most recent” and keep less recent in database for archival.