US Census Data as a MySQL Database Playground

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!

This site runs on the Thesis WordPress Theme

Thesis Theme thumbnail

If you're someone who doesn't understand a lot of PHP, HTML, or CSS, Thesis will give you a ton of functionality without having to alter any code. For the advanced, Thesis has incredible customization possibilities via extensive hooks and filters. And with so many design options, you can use the template over and over and never have it look like the same site.

If you're more familiar with how websites work, you can use the fantastic Thesis User's Guide and world-class support forums to make more professional customizations than you ever thought possible. The theme is not only highly customizable, but it allows me to build sites with a much more targeted focus on monetization than ever before. You can find out more about Thesis below:

Comments on this entry are closed.

Previous post:

Next post: