CREATE DATABASE mchd;
USE mchd;

CREATE TABLE  `mchd`.`establishments` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `mchd_id` varchar(25) default NULL,
  `name` varchar(255) NOT NULL,
  `detail_link` text,
  `address` varchar(255) default NULL,
  `address_extra` varchar(255) default NULL,
  `city` varchar(150) default NULL,
  `postal_code` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `mchd_id` (`mchd_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6875 DEFAULT CHARSET=utf8

CREATE TABLE  `mchd`.`inspections` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `mchd_id` varchar(25) default NULL,
  `number` int(10) unsigned default NULL,
  `score` tinyint(3) unsigned default NULL,
  `type` varchar(100) default NULL,
  `inspection_date` datetime default NULL,
  `scrape_date` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `number` (`number`),
  KEY `mchd_id` (`mchd_id`),
  KEY `type` (`type`),
  KEY `inspection_date` (`inspection_date`)
) ENGINE=MyISAM AUTO_INCREMENT=7127 DEFAULT CHARSET=utf8

CREATE TABLE  `mchd`.`establishment_geo_data` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `mchd_id` varchar(25) default NULL,
  `address` text,
  `country_name_code` char(5) default NULL,
  `country_name` varchar(200) default NULL,
  `area_name` varchar(200) default NULL,
  `locality_name` varchar(200) default NULL,
  `thoroughfare_name` text,
  `postal_code` varchar(50) default NULL,
  `accuracy` tinyint(3) unsigned default NULL,
  `lat_lon_box_n` decimal(15,11) default NULL,
  `lat_lon_box_s` decimal(15,11) default NULL,
  `lat_lon_box_e` decimal(15,11) default NULL,
  `lat_lon_box_w` decimal(15,11) default NULL,
  `latitude` decimal(15,11) default NULL,
  `longitude` decimal(15,11) default NULL,
  `processed_date` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `mchd_id` (`mchd_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9836 DEFAULT CHARSET=utf8