Skip to content

Instantly share code, notes, and snippets.

@jasonclark

jasonclark/data-model-people.sql

Created July 28, 2016 16:44
Show Gist options
  • Save jasonclark/dc86dad1ed0819c30a3ee5c6b2084793 to your computer and use it in GitHub Desktop.
Save jasonclark/dc86dad1ed0819c30a3ee5c6b2084793 to your computer and use it in GitHub Desktop.
Download ZIP
Data model for @msulibrary people app
Raw
data-model-people.sql
--
-- Database: `people`
--
-- --------------------------------------------------------
--
-- Table structure for table `create_action`
--
CREATE TABLE IF NOT EXISTS `create_action` (
`action_id` int(6) NOT NULL AUTO_INCREMENT,
`object` varchar(255) DEFAULT NULL COMMENT 'expertise label',
`object_uri` varchar(255) DEFAULT NULL COMMENT 'expertise linked data URI',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`action_id`),
FULLTEXT KEY `create_index` (`object`,`object_uri`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=43 ;
-- --------------------------------------------------------
--
-- Table structure for table `create_action_match`
--
CREATE TABLE IF NOT EXISTS `create_action_match` (
`person_id` int(8) NOT NULL DEFAULT '0',
`action_id` int(8) NOT NULL DEFAULT '0',
PRIMARY KEY (`person_id`,`action_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `interact_action`
--
CREATE TABLE IF NOT EXISTS `interact_action` (
`action_id` int(6) NOT NULL AUTO_INCREMENT,
`object` varchar(255) DEFAULT NULL COMMENT 'liaison role label',
`object_uri` varchar(255) DEFAULT NULL COMMENT 'liaison role linked data URI',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`action_id`),
FULLTEXT KEY `interact_index` (`object`,`object_uri`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `interact_action_match`
--
CREATE TABLE IF NOT EXISTS `interact_action_match` (
`person_id` int(8) NOT NULL DEFAULT '0',
`action_id` int(8) NOT NULL DEFAULT '0',
PRIMARY KEY (`person_id`,`action_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `person`
--
CREATE TABLE IF NOT EXISTS `person` (
`person_id` int(6) NOT NULL AUTO_INCREMENT,
`person_libcal_id` varchar(6) NOT NULL,
`familyName` varchar(150) DEFAULT NULL,
`givenName` varchar(75) DEFAULT NULL,
`person_dept` varchar(255) DEFAULT NULL,
`person_room` varchar(10) DEFAULT 'unassigned',
`jobTitle` text,
`person_phone` varchar(20) DEFAULT NULL,
`person_fax` varchar(20) DEFAULT 'unassigned',
`person_email` varchar(80) DEFAULT NULL,
`person_im` varchar(40) DEFAULT NULL,
`person_web` varchar(255) DEFAULT NULL,
`person_vita` varchar(255) DEFAULT NULL,
`person_category` varchar(125) DEFAULT NULL COMMENT 'faculty, staff, or student',
`person_role` varchar(50) DEFAULT 'unassigned',
`person_primary_liaison` varchar(125) DEFAULT NULL,
`person_secondary_liaison` varchar(125) DEFAULT NULL,
`affiliation` varchar(255) DEFAULT 'Montana State University (MSU)',
`sameAs` varchar(255) DEFAULT NULL,
`sameAs2` varchar(255) DEFAULT NULL,
`person_image` varchar(255) DEFAULT NULL,
`person_description` text,
`person_password` varchar(16) DEFAULT NULL,
`person_privacy` varchar(10) DEFAULT 'n',
`person_status` varchar(10) NOT NULL DEFAULT 'a',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`person_id`),
FULLTEXT KEY `full_index` (`givenName`,`familyName`,`jobTitle`,`person_dept`,`person_description`),
FULLTEXT KEY `staff_primary_liaison` (`staff_primary_liaison`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment