Election software/sql

From Wikimedia Australia
Jump to navigation Jump to search
CREATE TABLE IF NOT EXISTS `Categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key - auto-increments',
  `name` varchar(255) NOT NULL COMMENT 'The name of the category',
  `order` int(11) NOT NULL COMMENT 'The order in which to display the categories',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

INSERT INTO `Categories` (`id`, `name`, `order`) VALUES
(1, 'President', 1),
(2, 'Vice President', 2),
(3, 'Secretary', 3),
(4, 'Treasurer', 4),
(5, 'Ordinary Officers', 5);

CREATE TABLE IF NOT EXISTS `Nominees` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key - auto-increments',
  `description` text NOT NULL COMMENT 'A description of the nominee. May include HTML.',
  `category` int(11) NOT NULL COMMENT 'Foreign key referencing ID in the Categories table',
  `year` int(11) NOT NULL COMMENT 'Year that teh nominee was nominated',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;

INSERT INTO `Nominees` (`id`, `description`, `category`, `year`) VALUES
(1, 'Bulbasaur', 1, 2011),
(2, 'Ivysaur', 1, 2011),
(3, 'Venusaur', 1, 2011),
(4, 'Charmander', 1, 2011),
(5, 'Charmeleon', 2, 2011),
(6, 'Charizard', 2, 2011),
(7, 'Squirtle', 2, 2011),
(8, 'Wartortle', 2, 2011),
(9, 'Blastoise', 3, 2011),
(10, 'Caterpie', 3, 2011),
(11, 'Metapod', 3, 2011),
(12, 'Butterfree', 4, 2011),
(13, 'Weedle', 4, 2011),
(14, 'Kakuna', 4, 2011),
(15, 'Beedrill', 5, 2011),
(16, 'Pidgey', 5, 2011),
(17, 'Pidgeotto', 5, 2011),
(18, 'Pidgeot', 5, 2011),
(19, 'Rattata', 5, 2011),
(20, 'Raticate', 5, 2011);

CREATE TABLE IF NOT EXISTS `Voters` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key - auto-increments',
  `name` varchar(255) NOT NULL COMMENT 'Username',
  `address` varchar(255) NOT NULL COMMENT 'User address',
  `email` varchar(255) NOT NULL COMMENT 'Email address',
  `password` varchar(150) NOT NULL COMMENT 'Encrypted password',
  `ip` varchar(27) NOT NULL COMMENT 'IP Address (supports IPv6)',
  `code` varchar(20) NOT NULL COMMENT 'Session code',
  `checked` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not the user has been checked to see if they can vote',
  `approved` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether or nto the user has been approved',
  `lastaccessed` datetime NOT NULL COMMENT 'Date and time of last access',
  `access` varchar(20) NOT NULL COMMENT 'The level of access for the user: admin for administrators',
  `votesubmitted` datetime NOT NULL COMMENT 'The date and time when they submitted their votes.',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `Votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key - auto-increments',
  `nomineeid` int(11) NOT NULL COMMENT 'Foreign key for the voters ID',
  `vote` int(11) NOT NULL COMMENT 'Integer value represnting the preference for a nominee by the voter',
  `voterid` int(11) NOT NULL COMMENT 'Foreign key to the voter ID',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;