Select Git revision
designsurvey.sql
designsurvey.sql 6.27 KiB
CREATE USER 'designsurvey'@'localhost' IDENTIFIED BY 'designsurvey';
GRANT USAGE ON * . * TO 'designsurvey'@'localhost' IDENTIFIED BY 'designsurvey' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE IF NOT EXISTS `designsurvey` ;
GRANT ALL PRIVILEGES ON `designsurvey` . * TO 'designsurvey'@'localhost';
USE `designsurvey`;
--
-- Table structure for table `age_groups`
--
CREATE TABLE IF NOT EXISTS `age_groups` (
`id` int(11) NOT NULL auto_increment,
`age_group` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `age_groups` (`id`, `age_group`) VALUES
(1, 'Under 18'),
(2, '18-24'),
(3, '25-49'),
(4, '50+');
-- --------------------------------------------------------
--
-- Table structure for table `audiences`
--
CREATE TABLE IF NOT EXISTS `audiences` (
`id` int(11) NOT NULL auto_increment,
`audience` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `audiences` (`id`, `audience`) VALUES
(1, 'Prospective Student'),
(2, 'Current Student'),
(3, 'Parent of a Prospective Student and/or Current Student'),
(4, 'Alum'),
(5, 'Faculty/Staff'),
(6, 'Nebraska Resident');
-- --------------------------------------------------------
--
-- Table structure for table `auth`
--
CREATE TABLE IF NOT EXISTS `auth` (
`username` varchar(50) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
PRIMARY KEY (`username`),
KEY `password` (`password`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `prototype`
--
CREATE TABLE IF NOT EXISTS `prototype` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(20) default NULL,
`splash_image` varchar(255) NOT NULL default '',
`full_image` varchar(255) default NULL,
`alt_image` varchar(255) default NULL,
`thumb_image` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `prototype` (`id`, `title`, `splash_image`, `full_image`, `alt_image`, `thumb_image`) VALUES
(1, 'A', 'splash/a.png', 'a.png', 'a_alt.png', 'thumbs/a.png'),
(2, 'B', 'splash/b.png', 'b.png', 'b_alt.png', 'thumbs/b.png'),
(3, 'C', 'splash/c.png', 'c.png', 'c_alt.png', 'thumbs/c.png');
-- --------------------------------------------------------
--
-- Table structure for table `prototype_ranking`
--
CREATE TABLE IF NOT EXISTS `prototype_ranking` (
`id` int(11) NOT NULL auto_increment,
`voter_id` int(11) NOT NULL default '0',
`prototype_id` int(11) NOT NULL default '0',
`rank` int(11) NOT NULL default '0',
`rank2` int(11) NOT NULL default '0',
`why_response` mediumtext,
`improve_response` mediumtext,
PRIMARY KEY (`id`),
KEY `voter_id` (`voter_id`,`prototype_id`,`rank`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
-- --------------------------------------------------------
--
-- Table structure for table `prototype_rating`
--
CREATE TABLE IF NOT EXISTS `prototype_rating` (
`id` int(11) NOT NULL auto_increment,
`voter_id` int(11) NOT NULL default '0',
`prototype_id` int(11) NOT NULL default '0',
`rating` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `voter_id` (`voter_id`,`prototype_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `prototype_sent`
--
CREATE TABLE IF NOT EXISTS `prototype_sent` (
`id` int(11) NOT NULL auto_increment,
`voter_id` int(11) NOT NULL default '0',
`prototype_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `voter_id` (`voter_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `questions`
--
CREATE TABLE IF NOT EXISTS `questions` (
`id` int(11) NOT NULL auto_increment,
`audience_id` int(11) NOT NULL default '0',
`question` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `audience_id` (`audience_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `rating_lead_questions`
--
CREATE TABLE IF NOT EXISTS `rating_lead_questions` (
`id` int(11) NOT NULL auto_increment,
`lead_question` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `rating_questions`
--
CREATE TABLE IF NOT EXISTS `rating_questions` (
`id` int(11) NOT NULL auto_increment,
`audience_id` int(11) NOT NULL default '0',
`lead_question_id` int(11) NOT NULL default '0',
`question` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `audience_id` (`audience_id`),
KEY `lead_question_id` (`lead_question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `rating_responses`
--
CREATE TABLE IF NOT EXISTS `rating_responses` (
`id` int(11) NOT NULL auto_increment,
`voter_id` int(11) NOT NULL default '0',
`question_id` int(11) NOT NULL default '0',
`response` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `voter_id` (`voter_id`,`question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `responses`
--
CREATE TABLE IF NOT EXISTS `responses` (
`id` int(11) NOT NULL auto_increment,
`voter_id` int(11) NOT NULL default '0',
`question_id` int(11) NOT NULL default '0',
`response` mediumtext,
PRIMARY KEY (`id`),
KEY `voter_id` (`voter_id`,`question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `voter`
--
CREATE TABLE IF NOT EXISTS `voter` (
`id` int(11) NOT NULL auto_increment,
`audience_id` int(11) default NULL,
`age_group_id` int(11) default NULL,
`gender` char(1) default NULL,
`ip` int(32) unsigned NOT NULL,
`step` int(10) unsigned default NULL,
`prototype_order` mediumtext,
`viewport` varchar(50) default NULL,
`submitdatetime` datetime default NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;