Ninedays Blog

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.