Sv5sp92P

1CREATE SCHEMA IF NOT EXISTS `projects` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
2USE `projects`;
3
4-- -----------------------------------------------------
5-- Table `projects`.`sw_states`
6-- -----------------------------------------------------
7DROP TABLE IF EXISTS `projects`.`sw_states` ;
8
9CREATE  TABLE IF NOT EXISTS `projects`.`sw_states` (
10  `id` INT(2) NOT NULL AUTO_INCREMENT COMMENT 'autoinc PK' ,
11  `state` VARCHAR(40) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Name of State' ,
12  `abbrv` CHAR(2) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL ,
13  PRIMARY KEY (`id`) ,
14  INDEX `statename` (`state` ASC) )
15ENGINE = MyISAM
16DEFAULT CHARACTER SET = utf8
17COLLATE = utf8_general_ci
18COMMENT = 'Holds State names and Abbreviations';
19
20
21-- -----------------------------------------------------
22-- Table `projects`.`sw_county`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `projects`.`sw_county` ;
25
26CREATE  TABLE IF NOT EXISTS `projects`.`sw_county` (
27  `id` INT(4) NOT NULL AUTO_INCREMENT ,
28  `countyname` VARCHAR(35) NOT NULL ,
29  PRIMARY KEY (`id`) ,
30  INDEX `cty` (`countyname` ASC) )
31ENGINE = MyISAM
32DEFAULT CHARACTER SET = utf8
33COLLATE = utf8_general_ci;
34
35
36-- -----------------------------------------------------
37-- Table `projects`.`sw_timezones`
38-- -----------------------------------------------------
39DROP TABLE IF EXISTS `projects`.`sw_timezones` ;
40
41CREATE  TABLE IF NOT EXISTS `projects`.`sw_timezones` (
42  `id` INT(2) NOT NULL AUTO_INCREMENT ,
43  `sw_timezonescol` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NULL ,
44  PRIMARY KEY (`id`) ,
45  INDEX `tz` (`sw_timezonescol` ASC) )
46ENGINE = MyISAM
47DEFAULT CHARACTER SET = utf8
48COLLATE = utf8_general_ci;
49
50
51-- -----------------------------------------------------
52-- Table `projects`.`sw_zips`
53-- -----------------------------------------------------
54DROP TABLE IF EXISTS `projects`.`sw_zips` ;
55
56CREATE  TABLE IF NOT EXISTS `projects`.`sw_zips` (
57  `id` INT(5) NOT NULL AUTO_INCREMENT ,
58  `city` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NULL COMMENT 'Name of City' ,
59  `areacode` INT(3) NULL COMMENT 'Number' ,
60  `lat` FLOAT NULL COMMENT 'latitude' ,
61  `lon` FLOAT NULL COMMENT 'longitude' ,
62  `gps` POINT NOT NULL COMMENT 'Creates a spatial GPS point\nUPDATE myTable SET g = PointFromText(CONCAT(\'POINT(\',lat,\' \',lon,\')\'));' ,
63  `sw_county_id` INT(4) NOT NULL ,
64  `sw_states_id` INT(2) NOT NULL ,
65  `sw_timezones_id` INT(2) NOT NULL ,
66  PRIMARY KEY (`id`, `sw_county_id`, `sw_states_id`, `sw_timezones_id`) ,
67  SPATIAL INDEX `Coordinates` (`gps` ASC) ,
68  INDEX `fk_sw_zips_sw_county` (`sw_county_id` ASC) ,
69  INDEX `fk_sw_zips_sw_states1` (`sw_states_id` ASC) ,
70  INDEX `fk_sw_zips_sw_timezones1` (`sw_timezones_id` ASC) ,
71  CONSTRAINT `fk_sw_zips_sw_county`
72    FOREIGN KEY (`sw_county_id` )
73    REFERENCES `projects`.`sw_county` (`id` )
74    ON DELETE NO ACTION
75    ON UPDATE NO ACTION,
76  CONSTRAINT `fk_sw_zips_sw_states1`
77    FOREIGN KEY (`sw_states_id` )
78    REFERENCES `projects`.`sw_states` (`id` )
79    ON DELETE NO ACTION
80    ON UPDATE NO ACTION,
81  CONSTRAINT `fk_sw_zips_sw_timezones1`
82    FOREIGN KEY (`sw_timezones_id` )
83    REFERENCES `projects`.`sw_timezones` (`id` )
84    ON DELETE NO ACTION
85    ON UPDATE NO ACTION)
86ENGINE = MyISAM
87DEFAULT CHARACTER SET = utf8
88COLLATE = utf8_general_ci
89ROW_FORMAT = DEFAULT;