-- MySQL dump 10.9

-- Host: localhost    Database: ocf
-- ------------------------------------------------------
-- Server version	4.1.11

--
-- Table structure for table `access`
--

DROP TABLE IF EXISTS `access`;
CREATE TABLE `access` (
  `UniqueID` int(10) unsigned default NULL,
  `Organization` text,
  `Division` text,
  `Changeable` tinyint(1) default NULL,
  `Year` year(4) default NULL,
  `Event` text,
  `Administrator` tinyint(1) default NULL,
  `Money` tinyint(1) default NULL,
  `Budget` tinyint(1) default NULL,
  `Staffing` tinyint(1) default NULL,
  `Status` tinyint(1) default NULL,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
  INDEX (`Year`, `Event`, `Organization`, `Division`),
  FOREIGN KEY (`Year`, `Event`, `Organization`, `Division`) REFERENCES budget(`Year`, `Event`, `Organization`, `Division`),
) ENGINE=INNODB;

--
-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
  `UniqueID` int(10) unsigned default NULL,
  `StreetAddress` text,
  `City` text,
  `State` text,
  `Country` text,
  `PostalCode` text,
  `AddressType` text,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

--
-- Table structure for table `allocation`
--

DROP TABLE IF EXISTS `allocation`;
CREATE TABLE `allocation` (
  `Year` year(4) default NULL,
  `Event` text,
  `Organization` text,
  `Division` text,
  `AllocatedBy` int(10) unsigned default NULL,
  `AllocatedWhen` datetime default NULL,
  `Class` text,
  `SubClass` text,
  `Quantity` int(10) unsigned default NULL,
  `FirstSerial` int(10) unsigned default NULL,
  `Purpose` text,
  INDEX (`Year`, `Event`, `Organization`, `Division`),
  FOREIGN KEY (`Year`, `Event`, `Organization`, `Division`) REFERENCES budget(`Year`, `Event`, `Organization`, `Division`),
  INDEX (`Year`, `Event`, `Class`, `SubClass`, `Purpose`),
  FOREIGN KEY (`Year`, `Event`, `Class`, `SubClass`, `Purpose`) REFERENCES purpose(`Year`, `Event`, `Class`, `SubClass`, `Purpose`),
) ENGINE=INNODB;

--
-- Table structure for table `budget`
--

DROP TABLE IF EXISTS `budget`;
CREATE TABLE `budget` (
  `Year` year(4) default NULL,
  `Event` text,
  `Organization` text,
  `Division` text,
  `PaidAsCrew` tinyint(1) default NULL,
  INDEX (`Year`, `Event`),
  INDEX (`Year`, `Event`, `Organization`, `Division`),
  FOREIGN KEY (`Year`, `Event`) REFERENCES settings(`Year`, `Event`),
) ENGINE=INNODB;

--
-- Table structure for table `days`
--

DROP TABLE IF EXISTS `days`;
CREATE TABLE `days` (
  `UniqueID` int(10) unsigned default NULL,
  `Year` year(4) default NULL,
  `Event` text,
  `Organization` text,
  `Division` text,
  `Day` date default NULL,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

--
-- Table structure for table `email`
--

DROP TABLE IF EXISTS `email`;
CREATE TABLE `email` (
  `UniqueID` int(10) unsigned default NULL,
  `Address` text,
  `EmailType` text,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

--
-- Table structure for table `hours`
--

DROP TABLE IF EXISTS `hours`;
CREATE TABLE `hours` (
  `Year` year(4) default NULL,
  `Event` text,
  `Organization` text,
  `Division` text,
  `Date` date default NULL,
  `Start` time default NULL,
  `End` time default NULL,
  `Extra` time default NULL,
  INDEX (`Year`, `Event`, `Organization`, `Division`),
  FOREIGN KEY (`Year`, `Event`, `Organization`, `Division`) REFERENCES budget(`Year`, `Event`, `Organization`, `Division`),
) ENGINE=INNODB;

--
-- Table structure for table `inventory`
--

DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory` (
  `Year` year(4) default NULL,
  `Event` text,
  `Class` text,
  `SubClass` text,
  `Quantity` int(10) unsigned default NULL,
  `FirstSerial` int(10) unsigned default NULL,
  `ReceivedBy` int(10) unsigned default NULL,
  `ReceivedWhen` datetime default NULL,
  INDEX (`Year`, `Event`),
  INDEX (`Year`, `Event`, `Class`, `SubClass`),
  FOREIGN KEY (`Year`, `Event`) REFERENCES settings(`Year`, `Event`),
) ENGINE=INNODB;

--
-- Table structure for table `inventory1038664220`
--

DROP TABLE IF EXISTS `inventory1038664220`;
CREATE TABLE `inventory1038664220` (
  `UniqueID` int(10) unsigned default NULL
) ENGINE=INNODB;

--
-- Table structure for table `inventory_type`
--

DROP TABLE IF EXISTS `inventory_type`;
CREATE TABLE `inventory_type` (
  `Year` year(4) default NULL,
  `Event` text,
  `Class` text,
  `SubClass` text,
  `Serialized` tinyint(1) default NULL,
  `PerEvent` tinyint(1) default NULL,
  `PriceEach` float default NULL,
  `PriceChangeDate` date default NULL,
  `NewPrice` float default NULL,
  `StartingBirthDate` date default NULL,
  `EndingBirthDate` date default NULL,
  `AvailableFrom` datetime default NULL,
  `AvailableTo` datetime default NULL,
  INDEX (`Year`, `Event`, `Class`, `SubClass`),
  FOREIGN KEY (`Year`, `Event`, `Class`, `SubClass`) REFERENCES inventory(`Year`, `Event`, `Class`, `SubClass`),
) ENGINE=INNODB;

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

DROP TABLE IF EXISTS `item`;
CREATE TABLE `item` (
  `Year` year(4) default NULL,
  `Event` text,
  `Organization` text,
  `Division` text,
  `Date` date default NULL,
  `Class` text,
  `SubClass` text,
  `Quantity` int(10) unsigned default NULL,
  `Purpose` text,
  INDEX (`Year`, `Event`, `Organization`, `Division`),
  FOREIGN KEY (`Year`, `Event`, `Organization`, `Division`) REFERENCES budget(`Year`, `Event`, `Organization`, `Division`),
  INDEX (`Year`, `Event`, `Class`, `SubClass`, `Purpose`),
  FOREIGN KEY (`Year`, `Event`, `Class`, `SubClass`, `Purpose`) REFERENCES purpose(`Year`, `Event`, `Class`, `SubClass`, `Purpose`),
) ENGINE=INNODB;

--
-- Table structure for table `management1431956943`
--

DROP TABLE IF EXISTS `management1431956943`;
CREATE TABLE `management1431956943` (
  `UniqueID` int(10) unsigned default NULL
) ENGINE=INNODB;

--
-- Table structure for table `message`
--

DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
  `UniqueID` int(10) unsigned default NULL,
  `SenderID` int(10) unsigned default NULL,
  `MessageID` int(10) unsigned NOT NULL auto_increment,
  `Date` datetime default NULL,
  `Message` text,
  PRIMARY KEY  (`MessageID`),
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

--
-- Table structure for table `ocf_data`
--

DROP TABLE IF EXISTS `ocf_data`;
CREATE TABLE `ocf_data` (
  `OCF_UniqueID` int(10) unsigned NOT NULL default '0',
  `OCF_Id` int(10) unsigned default NULL,
  `LastName` text,
  `FirstName` text,
  `Address` text,
  `City` text,
  `State` text,
  `ZipCode` text,
  `BoothNumber` int(10) unsigned default NULL,
  `FFN_Mailing` tinyint(1) default NULL,
  `Absentee` varchar(4) default NULL,
  `Verified_Member` tinyint(1) default NULL,
  `Verified_How` text,
  `BoothRep` tinyint(1) default NULL,
  `Missing` text,
  `LastFair` year(4) default NULL,
  `HomePhone` text,
  `Message` text,
  `WorkPhone` text,
  `Pager` text,
  `Leader` text,
  `RoleCode` text,
  `CrewCode` text,
  `Role` text,
  `MemberDate` date default NULL,
  `Country` text,
  `Email` text,
  `PersonID` int(10) unsigned default NULL,
  `BoothRep2` tinyint(1) default NULL,
  `BoothNumber2` int(10) unsigned default NULL,
  `BoothStatus` text,
  `RepStatus` text,
  `BoothName` text,
  `BoothType` text,
  `AddressRelease` tinyint(1) default NULL,
  `MailLabel` tinyint(1) default NULL,
  `DataOrigin` char(1) default NULL,
  `Import` text,
  `Address2` text,
  `City2` text,
  `State2` text,
  `ZipCode2` text,
  `HomePhone2` text,
  `WorkPhone2` text,
  `Message2` text,
  `Pager2` text,
  `Email2` text,
  `RecordNumber` int(10) unsigned default NULL,
  `BoothList` tinyint(1) default NULL,
  `Crew` text,
  PRIMARY KEY  (`OCF_UniqueID`)
) ENGINE=INNODB;

--
-- Table structure for table `ocf_merrill`
--

DROP TABLE IF EXISTS `ocf_merrill`;
CREATE TABLE `ocf_merrill` (
  `Crew` text,
  `LastName` text,
  `FirstName` text,
  `BandType` text,
  `Middle` text,
  `NickName` text,
  `Title` text
) ENGINE=INNODB;

--
-- Table structure for table `password`
--

DROP TABLE IF EXISTS `password`;
CREATE TABLE `password` (
  `UniqueID` int(10) unsigned default NULL,
  `Login` text,
  `Password` text,
  `Program` text,
  `Locked` tinyint(1) default NULL,
  `Hackery` tinyint(1) default NULL,
  `Coordinator` tinyint(1) default NULL,
  `Contacts` tinyint(1) default NULL,
  `People` tinyint(1) default NULL,
  `Logs` tinyint(1) default NULL,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

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

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `UniqueID` int(10) unsigned NOT NULL auto_increment,
  `LastName` text,
  `FirstName` text,
  `MiddleName` text,
  `NickName` text,
  `Title` text,
  `TeeShirtSize` text,
  `CrisisTraining` date default NULL,
  `PathRoveTraining` date default NULL,
  `Membership` date default NULL,
  `BallotType` text,
  `DriversLicense` text,
  `Birthday` date default NULL,
  `ContactSeparately` tinyint(1) default NULL,
  `GetsFFN` tinyint(1) default NULL,
  `Notes` text,
  PRIMARY KEY  (`UniqueID`)
) ENGINE=INNODB;

--
-- Table structure for table `phone`
--

DROP TABLE IF EXISTS `phone`;
CREATE TABLE `phone` (
  `UniqueID` int(10) unsigned default NULL,
  `Number` text,
  `PhoneType` text,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

--
-- Table structure for table `preferences`
--

DROP TABLE IF EXISTS `preferences`;
CREATE TABLE `preferences` (
  `UniqueID` int(10) unsigned default NULL,
  `SaveSettings` tinyint(1) default NULL,
  `ShowDependents` tinyint(1) default NULL,
  `ShowComments` tinyint(1) default NULL,
  `Year` year(4) default NULL,
  `QuickList` tinyint(1) default NULL,
  `OverBudgetColor` varchar(10) default 'red',
  `UnderBudgetColor` varchar(10) default 'lime',
  `UnpaidColor` varchar(10) default 'red',
  `EditLinkColor` varchar(10) default 'black',
  `EditHoverColor` varchar(10) default 'navy',
  `EmailLinkColor` varchar(10) default 'blue',
  `EmailHoverColor` varchar(10) default 'blue',
  `ErrorMessageColor` varchar(10) default 'red',
  `ShowAddresses` tinyint(1) default NULL,
  `ShowPhones` tinyint(1) default NULL,
  `ShowEmails` tinyint(1) default NULL,
  `SuppressBlanks` tinyint(1) default NULL,
  `MembersOnly` tinyint(1) default NULL,
  `ShowAllDays` tinyint(1) default NULL,
  `ShowHours` tinyint(1) default NULL,
  `ShowDescriptions` tinyint(1) default NULL,
  `AutoCalculate` tinyint(1) default NULL,
  `Event` text,
  `BudgetExpand` text,
  `AdminExpand` text,
  `BudgetGroups` text,
  `StaffGroups` text,
  `CurrentStaff` text,
  `SetPrices` tinyint(1) default NULL,
  `ShowReceivedDetails` tinyint(1) default NULL,
  `ShowAllocatedDetails` tinyint(1) default NULL,
  `ShowInventoryLog` tinyint(1) default NULL,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

--
-- Table structure for table `purpose`
--

DROP TABLE IF EXISTS `purpose`;
CREATE TABLE `purpose` (
  `Year` year(4) default NULL,
  `Event` text,
  `Class` text,
  `SubClass` text,
  `Purpose` text,
  `StartingBirthDate` date default NULL,
  `EndingBirthDate` date default NULL,
  `Price` float default NULL,
  `NewPrice` float default NULL,
  `ChangeDate` date default NULL,
  INDEX (`Year`, `Event`, `Class`, `SubClass`),
  FOREIGN KEY (`Year`, `Event`, `Class`, `SubClass`) REFERENCES inventory(`Year`, `Event`, `Class`, `SubClass`),
  INDEX (`Year`, `Event`, `Class`, `SubClass`, `Purpose`),
) ENGINE=INNODB;

--
-- Table structure for table `received`
--

DROP TABLE IF EXISTS `received`;
CREATE TABLE `received` (
  `UniqueID` int(10) unsigned default NULL,
  `Year` year(4) default NULL,
  `Organization` text,
  `Division` text,
  `Event` text,
  `Class` text,
  `SubClass` text,
  `Purpose` text,
  `Quantity` int(10) unsigned default NULL,
  `FirstSerial` int(10) unsigned default NULL,
  `AmountReceived` float default NULL,
  `ReceivedWhen` date default NULL,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),    
  INDEX (`Year`, `Event`, `Organization`, `Division`),
  FOREIGN KEY (`Year`, `Event`, `Organization`, `Division`) REFERENCES budget(`Year`, `Event`, `Organization`, `Division`),
  INDEX (`Year`, `Event`, `Class`, `SubClass`, `Purpose`),
  FOREIGN KEY (`Year`, `Event`, `Class`, `SubClass`, `Purpose`) REFERENCES purpose(`Year`, `Event`, `Class`, `SubClass`, `Purpose`),
) ENGINE=INNODB;

--
-- Table structure for table `role`
--

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `UniqueID` int(10) unsigned default NULL,
  `Year` year(4) default NULL,
  `Event` text,
  `Organization` text,
  `Division` text,
  `Reference` int(10) unsigned default NULL,
  `Notes` text,
  `Title` text,
  `Type` text,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
  INDEX (`Year`, `Event`, `Organization`, `Division`),
  FOREIGN KEY (`Year`, `Event`, `Organization`, `Division`) REFERENCES budget(`Year`, `Event`, `Organization`, `Division`),
) ENGINE=INNODB;

--
-- Table structure for table `session`
--

DROP TABLE IF EXISTS `session`;
CREATE TABLE `session` (
  `UniqueID` int(10) unsigned default NULL,
  `LoggedIn` datetime default NULL,
  `LastHeardFrom` datetime default NULL,
  `LastQueryString` text,
  `ReadOnly` tinyint(1) default NULL,
  `SessionID` text,
  `SaveSettings` tinyint(1) default NULL,
  `ShowDependents` tinyint(1) default NULL,
  `ShowComments` tinyint(1) default NULL,
  `Year` year(4) default NULL,
  `IPAddress` text,
  `QuickList` tinyint(1) default NULL,
  `OverBudgetColor` varchar(10) default 'red',
  `UnderBudgetColor` varchar(10) default 'lime',
  `UnpaidColor` varchar(10) default 'red',
  `EditLinkColor` varchar(10) default 'black',
  `EditHoverColor` varchar(10) default 'navy',
  `EmailLinkColor` varchar(10) default 'blue',
  `EmailHoverColor` varchar(10) default 'blue',
  `ErrorMessageColor` varchar(10) default 'red',
  `ShowAddresses` tinyint(1) default NULL,
  `ShowPhones` tinyint(1) default NULL,
  `ShowEmails` tinyint(1) default NULL,
  `SuppressBlanks` tinyint(1) default NULL,
  `MembersOnly` tinyint(1) default NULL,
  `ShowAllDays` tinyint(1) default NULL,
  `ShowHours` tinyint(1) default NULL,
  `ShowDescriptions` tinyint(1) default NULL,
  `AutoCalculate` tinyint(1) default NULL,
  `Event` text,
  `BudgetExpand` text,
  `AdminExpand` text,
  `BudgetGroups` text,
  `StaffGroups` text,
  `CurrentStaff` text,
  `SetPrices` tinyint(1) default NULL,
  `ShowReceivedDetails` tinyint(1) default NULL,
  `ShowAllocatedDetails` tinyint(1) default NULL,
  `ShowInventoryLog` tinyint(1) default NULL,
  INDEX (`UniqueID`),
  FOREIGN KEY (`UniqueID`) REFERENCES person(`UniqueID`),
) ENGINE=INNODB;

--
-- Table structure for table `settings`
--

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `Year` year(4) default NULL,
  `Event` text,
  `PriceSOP` int(10) unsigned default NULL,
  `PriceTeen` int(10) unsigned default NULL,
  `PriceYouth` int(10) unsigned default NULL,
  `PriceCar` int(10) unsigned default NULL,
  `PricePaidCar` int(10) unsigned default NULL,
  `PriceOffsiteCar` int(10) unsigned default NULL,
  `AgeYouth` int(10) unsigned default NULL,
  `AgeTeen` int(10) unsigned default NULL,
  `AgeSOP` int(10) unsigned default NULL,
  INDEX (`Year`, `Event`),
) ENGINE=INNODB;

