-- phpMyAdmin SQL Dump
-- version 2.11.9.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 29, 2009 at 05:29 PM
-- Server version: 5.0.67
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `library`
--

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

--
-- Table structure for table `cast`
--

CREATE TABLE `cast` (
  `item` smallint(6) unsigned NOT NULL default '0',
  `seq` tinyint(2) NOT NULL default '0',
  `person` smallint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`item`,`seq`,`person`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `copy`
--

CREATE TABLE `copy` (
  `id` smallint(6) unsigned NOT NULL auto_increment,
  `item` smallint(6) unsigned NOT NULL default '0',
  `media` tinyint(4) unsigned NOT NULL default '0',
  `added` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`),
  KEY `item` (`item`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=652 ;

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

--
-- Table structure for table `genre`
--

CREATE TABLE `genre` (
  `id` smallint(4) unsigned NOT NULL default '0',
  `name` varchar(30) NOT NULL default '',
  `type` tinyint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `group`
--

CREATE TABLE `group` (
  `group` smallint(6) unsigned NOT NULL default '0',
  `person` smallint(6) unsigned NOT NULL default '0',
  `notes` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`group`,`person`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `item`
--

CREATE TABLE `item` (
  `id` smallint(6) unsigned NOT NULL auto_increment,
  `seq` smallint(3) unsigned NOT NULL default '0',
  `author` smallint(6) unsigned NOT NULL default '0',
  `authnotes` varchar(15) NOT NULL default '',
  `quote` char(1) NOT NULL default '',
  `pre` char(3) NOT NULL default '',
  `title` varchar(90) NOT NULL default '',
  `year` year(4) NOT NULL default '0000',
  `media` tinyint(4) unsigned NOT NULL default '0',
  `units` tinyint(2) unsigned NOT NULL default '1',
  `length` smallint(6) unsigned NOT NULL default '0',
  `genre` smallint(4) unsigned NOT NULL default '0',
  `mediarel` date NOT NULL default '0000-00-00',
  `added` date NOT NULL default '0000-00-00',
  `asin` varchar(10) NOT NULL,
  `imdb` varchar(10) NOT NULL default '',
  `isbn` varchar(20) NOT NULL,
  `ean` varchar(13) NOT NULL,
  `journal` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`,`seq`),
  KEY `genre` (`genre`),
  KEY `author` (`author`),
  KEY `mediarel` (`mediarel`),
  KEY `item` (`title`,`year`,`media`),
  KEY `added` (`added`),
  KEY `media` (`media`),
  KEY `ean` (`ean`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1101 ;

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

--
-- Table structure for table `loan`
--

CREATE TABLE `loan` (
  `item` smallint(6) unsigned NOT NULL default '0',
  `copy` smallint(6) unsigned NOT NULL default '0',
  `returned` date NOT NULL default '0000-00-00',
  `borrower` smallint(6) unsigned NOT NULL default '0',
  `added` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`item`,`copy`,`returned`),
  KEY `borrower` (`borrower`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `media`
--

CREATE TABLE `media` (
  `id` tinyint(4) unsigned NOT NULL auto_increment,
  `name` varchar(10) NOT NULL default '',
  `type` tinyint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`,`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

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

--
-- Table structure for table `person`
--

CREATE TABLE `person` (
  `id` smallint(6) unsigned NOT NULL auto_increment,
  `lname` varchar(50) NOT NULL default '',
  `fname` varchar(30) NOT NULL default '',
  `suffix` varchar(35) NOT NULL,
  `pre` varchar(6) NOT NULL default '',
  `pnote` varchar(15) NOT NULL default '',
  `imdb` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`lname`,`fname`,`suffix`,`pnote`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1279 ;

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

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

CREATE TABLE `type` (
  `id` tinyint(4) unsigned NOT NULL auto_increment,
  `name` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

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

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` smallint(6) unsigned NOT NULL auto_increment,
  `lname` varchar(30) NOT NULL default '',
  `fname` varchar(30) NOT NULL default '',
  `email` varchar(40) NOT NULL default '',
  `login` varchar(15) NOT NULL default '',
  `librarian` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`lname`,`fname`),
  UNIQUE KEY `login` (`login`),
  KEY `librarian` (`librarian`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
