Ninedays Blog

While looking for data to use while developing some play databases I discovered the US Census which is a great place to get some extraordinarily usable data. This data would be great to test scripts on as well as practice creating usable interfaces when faced with a lot of information. Some of what I learn and make from the census might actually be good in a code library to be used on a real project the way it is. I mean it is real and relevant data!

I wanted to create a database with tables using two of the three kinds of table relationships.

• One to one
• One to many
• Many to many

A good example using census data would be a system to track users, where they live, and what states they've been to and when.

I'd also like to throw in the ability to categorize the states by their region and divistion as designated by the US Census Bureau.

Read about database normalization to figure out how I made these tables. This entry will not be my platform to teach you database normalization. Maybe another day!

Database Design

## users Table
----------------------------------------------------------------
| id | fname | lname | email | dob | gender | residencestateid |
|    |       |       |       |     |        |                  |
----------------------------------------------------------------

## user_statevisited_rel Table

--------------------------------
| id | userid | stateid | year |
|    |        |         |      |
--------------------------------

## us_states Table
-----------------------------------------------------------------------
| id | name | divisionid | population1990 | areaimperial | areametric |
|    |      |            |                |              |            |
-----------------------------------------------------------------------

## us_divisions Table
-----------------------------------
| id | name | censusid | regionid |
|    |      |          |          |
-----------------------------------

## us_regions Table
------------------------
| id | name | censusid |
|    |      |          |
------------------------

With those tables I have created the following relationships:

• One to one
  • none (these aren't that common, trust me)
• One to Many
  • user(residencestateid) -> us_states(id)
  • divisions(regionid) -> us_regions(id)
  • state(divisionid) -> divisions(id)
• Many to Many
  • users(id)->userstatevisitedrel(userid & stateid) -> us_states(id)

In order to create a random set of users I'll also need two other tables to generate that data. The data for the following (1990_census_surname and 1990_census_firstname) tables will come from the 1990 US Census.

Database Design

## 1990_census_surnames Table
------------------------------------------
| id | name | frequency | cumfreq | rank |
|    |      |           |         |      |
------------------------------------------

## 1990_census_firstnames Table
---------------------------------------------------
| id | name | frequency | cumfreq | rank | gender |
|    |      |           |         |      |        |
---------------------------------------------------

This is gonna be fun to make, stay tuned over the next month (or so) while we get our MySQL database playground setup!