Data model for @msulibrary resources app

data-model-resources.sql

--
-- Database: `resources`
--

-- --------------------------------------------------------


--
-- Table structure for table `matchSubject`
--

CREATE TABLE IF NOT EXISTS `matchSubject` (
  `resource_id` int(8) NOT NULL DEFAULT '0',
  `subject_id` int(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`resource_id`,`subject_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `matchTopic`
--

CREATE TABLE IF NOT EXISTS `matchTopic` (
  `resource_id` int(8) NOT NULL DEFAULT '0',
  `topic_id` int(8) NOT NULL DEFAULT '0',
  KEY `resource_id` (`resource_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `matchType`
--

CREATE TABLE IF NOT EXISTS `matchType` (
  `resource_id` int(8) NOT NULL DEFAULT '0',
  `type_id` int(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`resource_id`,`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `resource`
--

CREATE TABLE IF NOT EXISTS `resource` (
  `resource_id` int(8) NOT NULL AUTO_INCREMENT,
  `resource_status` varchar(5) NOT NULL DEFAULT 'a',
  `resource_visits` int(10) DEFAULT '0' COMMENT 'visit count',
  `dc_title` text NOT NULL,
  `dc_identifier` text NOT NULL,
  `dc_publisher` varchar(255) DEFAULT NULL COMMENT 'Vendor',
  `dc_creator_id` int(3) NOT NULL DEFAULT '58',
  `dc_description` text NOT NULL,
  `dc_coverage` varchar(75) DEFAULT NULL,
  `dc_dateModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dc_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'element_subelement - date record created',
  `dc_access` text,
  `dc_use_note` text,
  `dc_note` text,
  `dc_keyword` text,
  PRIMARY KEY (`resource_id`),
  KEY `resource_status` (`resource_status`),
  FULLTEXT KEY `full_index` (`dc_title`,`dc_description`,`dc_use_note`,`dc_keyword`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

-- --------------------------------------------------------

--
-- Table structure for table `subject`
--

CREATE TABLE IF NOT EXISTS `subject` (
  `subject_id` int(8) NOT NULL AUTO_INCREMENT,
  `springshare_subject_id` int(8) DEFAULT NULL COMMENT 'identifier for subjects in springhare products',
  `dc_subject` varchar(255) NOT NULL DEFAULT 'reference' COMMENT `discipline-based controlled vocabulary based on university course catalog`,
  `dc_subjectCode` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`subject_id`),
  KEY `dc_subject` (`dc_subject`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

-- --------------------------------------------------------

--
-- Table structure for table `topic`
--

CREATE TABLE IF NOT EXISTS `topic` (
  `topic_id` int(8) NOT NULL AUTO_INCREMENT,
  `topic` varchar(255) DEFAULT NULL COMMENT 'topic preferred label',
  `topic_uri` varchar(100) NOT NULL DEFAULT 'topic link' COMMENT `dbpedia topic linked data URI`,
  PRIMARY KEY (`topic_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

-- --------------------------------------------------------

--
-- Table structure for table `type`
--

CREATE TABLE IF NOT EXISTS `type` (
  `type_id` int(8) NOT NULL AUTO_INCREMENT,
  `dc_type` varchar(255) NOT NULL DEFAULT 'database' COMMENT `generic types for resources`,
  PRIMARY KEY (`type_id`),
  KEY `dc_type` (`dc_type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;