-- phpMyAdmin SQL Dump
-- version 2.8.2.4
-- http://www.phpmyadmin.net
-- 
-- Host: localhost:3306
-- Generation Time: Sep 18, 2011 at 05:20 PM
-- Server version: 5.0.45
-- PHP Version: 5.0.5
-- 
-- Database: `beerapp`
-- 

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

-- 
-- Table structure for table `beer`
-- 

CREATE TABLE `beer` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `alcohol` decimal(3,1) NOT NULL,
  `id_style` int(11) NOT NULL,
  `id_brewery` int(11) NOT NULL,
  `dirty` tinyint(1) NOT NULL default '0',
  `comment` text,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `FK_STYLE` (`id_style`),
  KEY `FK_BREWERY` (`id_brewery`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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

-- 
-- Table structure for table `brewery`
-- 

CREATE TABLE `brewery` (
  `id` int(11) NOT NULL auto_increment,
  `id_country` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `dirty` tinyint(1) NOT NULL default '0',
  `comment` text,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `FK_COUNTRY` (`id_country`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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

-- 
-- Table structure for table `country`
-- 

CREATE TABLE `country` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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

-- 
-- Table structure for table `review`
-- 

CREATE TABLE `review` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `score` int(2) NOT NULL,
  `comment` text NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_beer` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_USER` (`id_user`),
  KEY `FK_BEER` (`id_beer`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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

-- 
-- Table structure for table `stock`
-- 

CREATE TABLE `stock` (
  `id` int(11) NOT NULL auto_increment,
  `id_user` int(11) NOT NULL,
  `id_beer` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_USER` (`id_user`),
  KEY `FK_BEER` (`id_beer`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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

-- 
-- Table structure for table `style`
-- 

CREATE TABLE `style` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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

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

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `pass` varchar(40) NOT NULL,
  `displayname` varchar(25) NOT NULL,
  `admin` tinyint(1) NOT NULL default '0',
  `active` tinyint(1) NOT NULL default '0',
  `activationkey` varchar(40) default NULL,
  `registration_time` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `displayname` (`displayname`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `beer`
-- 
ALTER TABLE `beer`
  ADD CONSTRAINT `beer_ibfk_1` FOREIGN KEY (`id_style`) REFERENCES `style` (`id`),
  ADD CONSTRAINT `beer_ibfk_2` FOREIGN KEY (`id_brewery`) REFERENCES `brewery` (`id`);

-- 
-- Constraints for table `brewery`
-- 
ALTER TABLE `brewery`
  ADD CONSTRAINT `brewery_ibfk_1` FOREIGN KEY (`id_country`) REFERENCES `country` (`id`);

-- 
-- Constraints for table `review`
-- 
ALTER TABLE `review`
  ADD CONSTRAINT `review_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`),
  ADD CONSTRAINT `review_ibfk_2` FOREIGN KEY (`id_beer`) REFERENCES `beer` (`id`);

-- 
-- Constraints for table `stock`
-- 
ALTER TABLE `stock`
  ADD CONSTRAINT `stock_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`),
  ADD CONSTRAINT `stock_ibfk_2` FOREIGN KEY (`id_beer`) REFERENCES `beer` (`id`);
