Populating the Regions, Divisions and States MySQL Tables
- US Census Data as a MySQL Database Playground
- Populating the Regions, Divisions and States MySQL Tables
- MySQL Queries Made Easy With PHP Functions Library
- Database Playground - Temporary Hiatus
- Making MySQL Update and Insert Easier
When I designed up the relationship between US regions, divisions and states I thought it out as 2 relationships which would ultimately define a 3rd relationship (indirectly.)
Direct Relationships
- divisions(
regionid) -> us_regions(id) - state(
divisionid) -> divisions(id)
Indirect Relationships
- state(
divisionid) -> divisions(id) | divisions(regionid) -> us_regions(id)
Making it obvious that I could query out with multiple joins what states are in a region even though the states table doesn't directly relate to the region table it's the state to division to region relationship that defines what states are in a region.
I thought about having regions and devisions in the same table and doing a self join but I didn't really see a benefit to using a parent/child relationship for these needs.
Enough talk, let's build the tables.
Starting with the regions:
MySQL
## us_regions Table
#------------------------
#| id | name | censusid |
#| | | |
#------------------------
CREATE TABLE `us_regions` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`censusid` SMALLINT UNSIGNED NOT NULL
) ENGINE = MYISAM;
The only reason I add censusid as a field to these tables is in case we ever need to reference back to the census, maybe a few years down the road with a feed or something. It's a small amount of data that could eventually be very helpful for relating to outside information.
Now divisions
MySQL
## us_divisions Table
#-----------------------------------
#| id | name | censusid | regionid |
#| | | | |
#-----------------------------------
CREATE TABLE `us_divisions` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`censusid` SMALLINT NOT NULL ,
`regionid` INT UNSIGNED NOT NULL
) ENGINE = MYISAM;
Populating Regions and Divisions
We could make a fancy application with a CMS and forms to populate this data but there's thirteen records total in these fields, I'll be doing it by hand.
MySQL
INSERT INTO `us_regions`(`id`,`name`,`censusid`)
VALUES (1 , 'Northeast', '1'),
(2 , 'Midwest', '2'),
(3 , 'South', '3'),
(4 , 'West', '4');
We had to define the regions first so we can make the relationship from divisions to regions when we manually add the divisions.
MySQL
INSERT INTO `us_divisions`(`id`,`name`,`censusid`,`regionid`)
VALUES (NULL , 'New England', '1', '1'),
(NULL , 'Middle Atlantic', '2', '1'),
(NULL , 'East North Central', '3', '2'),
(NULL , 'West North Central', '4', '2'),
(NULL , 'South Atlantic', '5', '3'),
(NULL , 'East South Central', '6', '3'),
(NULL , 'West South Central', '7', '3'),
(NULL , 'Mountain', '8', '4'),
(NULL , 'Pacific', '9', '4');
This time we let the auto-increment id do it's thing, we implicitly defined it in the regions table so we could be sure we had the right id for the relationships.
the newest discoveries, stories and shared tips!Come on, all the cool kids are doing it ;)


