{"id":618,"date":"2013-09-19T14:13:57","date_gmt":"2013-09-19T18:13:57","guid":{"rendered":"http:\/\/blog.uvm.edu\/ctl-projects\/?p=618"},"modified":"2013-09-19T14:13:57","modified_gmt":"2013-09-19T18:13:57","slug":"med-college-policies-db","status":"publish","type":"post","link":"https:\/\/blog.uvm.edu\/waw\/2013\/09\/19\/med-college-policies-db\/","title":{"rendered":"Med College policies db"},"content":{"rendered":"
\n
\n
Entered on 09\/19\/2013 at 13:53:44 EDT (GMT-0400) by Melissa Long<\/div>\n

I\u2019ve 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\/<\/a>). We absolutely\u00a0LOVE\u00a0the way the main campus Compliance Office has organized the University\u2019s policy website (http:\/\/www.uvm.edu\/policies\/?Page=alphalist.php<\/a>)and plan to link to it under the \u2018Policy Section\u2019 of our forthcoming \u201cdocument.\u201d However, COM also has some policies unique to our program that we\u2019d like to organize and present much like on the University\u2019s parent site.<\/em><\/p>\n

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\u2019m 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\u2019t struggle trying to re-invent a wheel.<\/em><\/p>\n

I\u2019m also curious about recommended practices around \u201cversioning\u201d and welcome your thoughts. Erica explained that her office doesn\u2019t 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.<\/em><\/p>\n

Is there any chance you\u2019d 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?<\/em><\/p><\/blockquote>\n

Entered on 09\/19\/2013 at 13:55:17 EDT (GMT-0400) by Wesley Wright:<\/p>\n<\/div>\n

 <\/p>\n

 <\/p>\n

The Policies database is a php application on www.uvm.edu interfaced with a MySQL database living on webdb.uvm.edu<\/div>\n
<\/div>\n
You can get an account on www.uvm.edu here:<\/div>\n
<\/div>\n
http:\/\/www.uvm.edu\/it\/account\/?Page=apply-department.html<\/div>\n
<\/div>\n
And you can get a mySQL database here:<\/div>\n
<\/div>\n
https:\/\/webdb.uvm.edu\/<\/div>\n
<\/div>\n
Polices db is pretty simple. Main table looks like this:<\/div>\n
<\/div>\n
CREATE TABLE `policy_docs` (
\n`doc_id` varchar(100) NOT NULL DEFAULT ”,
\n`filename` varchar(100) DEFAULT NULL,
\n`title2` varchar(200) DEFAULT NULL,
\n`category` varchar(255) DEFAULT NULL,
\n`official` varchar(255) DEFAULT NULL,
\n`review_year` char(10) DEFAULT NULL,
\n`status` char(2) DEFAULT NULL,
\n`board` char(2) DEFAULT NULL,
\nPRIMARY KEY (`doc_id`)
\n)<\/div>\n
<\/div>\n
doc_id is compliance internal numbering scheme (http:\/\/www.uvm.edu\/policies\/?Page=numberingsystem.html&SM=SubProcessMenu.html)<\/div>\n
filename is name\/location of PDF<\/div>\n
title2 is full document title<\/div>\n
category is comma delimited list of applicable category codes, related to\u00a0table categories<\/div>\n
official is code for responsible office\/officer, see table officials<\/div>\n
review_year is sort of a last reviewed\/next review date<\/div>\n
status is code letter, f for final, d for draft<\/div>\n
borad is\u00a0Governance code b\u00a0Board of Trustees\u00a0Institutional<\/div>\n
<\/div>\n
<\/div>\n
Other tables:<\/div>\n
<\/div>\n
# Dump of table categories
\n# ————————————————————CREATE TABLE `categories` (
\n`code` char(2) NOT NULL DEFAULT ”,
\n`cat_name` varchar(255) DEFAULT NULL,
\nPRIMARY KEY (`code`)
\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;<\/p>\n

# Dump of table community
\n# ————————————————————<\/p>\n

CREATE TABLE `community` (
\n`comm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
\n`doc_id` varchar(100) NOT NULL DEFAULT ”,
\n`community` varchar(100) NOT NULL DEFAULT ”,
\nPRIMARY KEY (`comm_id`)
\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1;<\/p>\n

# Dump of table officials
\n# ————————————————————<\/p>\n

CREATE TABLE `officials` (
\n`code` char(4) NOT NULL DEFAULT ”,
\n`official` varchar(255) DEFAULT NULL,
\nPRIMARY KEY (`code`)
\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;<\/p>\n

Typical SQL:<\/p>\n<\/div>\n

<\/div>\n
SELECT policy_docs.doc_id, policy_docs.title2,policy_docs.filename, policy_docs.category, board from policy_docs where status=’f’ order by title2<\/div>\n
<\/div>\n
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.<\/div>\n<\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"

Entered on 09\/19\/2013 at 13:53:44 EDT (GMT-0400) by Melissa Long I\u2019ve 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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41826],"tags":[42758,12386,647],"class_list":["post-618","post","type-post","status-publish","format-standard","hentry","category-wes","tag-boffins","tag-database","tag-php"],"_links":{"self":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/posts\/618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/comments?post=618"}],"version-history":[{"count":0,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/posts\/618\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/media?parent=618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/categories?post=618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.uvm.edu\/waw\/wp-json\/wp\/v2\/tags?post=618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}