-- MySQL dump 10.13  Distrib 5.1.37, for Win32 (ia32)
--
-- Host: pre-school.org.uk    Database: 
-- ------------------------------------------------------
-- Server version	5.1.37-community-log

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `acme`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `acme` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `acme`;

--
-- Table structure for table `addresses`
--

DROP TABLE IF EXISTS `addresses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `training_person_id` int(11) DEFAULT NULL,
  `company` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `firstname` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `line1` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `line2` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `line3` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `line4` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `town` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `county` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `postcode` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'United Kingdom',
  `phone` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_2` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_2` (`user_id`,`company`,`firstname`,`lastname`,`line1`,`line2`,`line3`,`town`,`county`,`postcode`,`country`,`training_person_id`),
  KEY `user_id` (`user_id`),
  KEY `deleted` (`deleted`),
  KEY `phone` (`phone`),
  KEY `training_person_id` (`training_person_id`),
  KEY `mobile` (`mobile`),
  CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17708 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT COMMENT='User addresses';
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `type` enum('Staff','Web') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Web',
  `username` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstname` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `company` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `member_no` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `member_type` enum('Pre-school Learning Alliance','SPPA','WPPA','IPPA','Early Years (NIPPA)') COLLATE utf8_unicode_ci DEFAULT NULL,
  `familiar` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `access_rights` set('Account Manager','Accreditation Administrator','Area Manager','Assessor','Director','Events Editor','FAQ Editor','News Editor','QI Administrator','QI Staff Member','Shop Administrator','Staff Member','Student','Tutor','Web Administrator','Website Editor') COLLATE utf8_unicode_ci DEFAULT NULL,
  `branch` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `address_id` int(11) DEFAULT NULL,
  `password` varchar(35) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `password_type` enum('MD5','Salted MD5','OSCommerce') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'MD5',
  `account_created` datetime DEFAULT NULL,
  `logincount` int(11) NOT NULL DEFAULT '0',
  `lastlogin` datetime DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `postcode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `osc_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `firstname` (`firstname`),
  KEY `type` (`type`),
  KEY `username` (`username`),
  KEY `password` (`password`),
  KEY `enabled` (`enabled`),
  KEY `postcode` (`postcode`),
  KEY `lastname` (`lastname`),
  KEY `office` (`branch`),
  KEY `access_rights` (`access_rights`),
  KEY `company` (`company`),
  KEY `member_no` (`member_no`),
  KEY `osc_id` (`osc_id`),
  KEY `member_type` (`member_type`),
  KEY `deleted` (`deleted`),
  KEY `account_created` (`account_created`),
  KEY `address_id` (`address_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4915 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
/*!40101 SET character_set_client = @saved_cs_client */;