Skip to content

Instantly share code, notes, and snippets.

@jrunning

jrunning/gist:1110438

Created July 27, 2011 21:40
Show Gist options
  • Save jrunning/1110438 to your computer and use it in GitHub Desktop.
Save jrunning/1110438 to your computer and use it in GitHub Desktop.
Download ZIP
Database Normalization 101
Raw
gistfile1.txt
!! BEFORE: one table
`some_table`
some_col | another_col | ascii_stuff
---------+-------------+------------
abc | ... | xyzzy
abc | ... | idspispopd
uvw | | idspispopd
wzy | | it is a good day to die
// QUERY
SELECT some_col, another_col, ascii_stuff
FROM some_table
WHERE some_col = 'abc'
// RESULT
some_col | another_col | ascii_stuff
---------+-------------+------------
abc | ... | xyzzy
abc | ... | idspispopd
!! AFTER: two tables
`some_table`
some_col | another_col | ascii_stuff_id // <-- foreign key
---------+-------------+---------------
abc | ... | 1
abc | ... | 2
| | 2
| | 4
`ascii_stuff`
id | ascii_data
----+------------------------
1 | xyzzy
2 | idspispopd
3 | porntipsguzzardo
4 | it is a good day to die
... | ...
// QUERY
SELECT some_col, another_col, ascii_stuff.ascii_data AS ascii_stuff
FROM some_table
JOIN ascii_stuff ON some_table.ascii_stuff_id = ascii_stuff.id
WHERE some_val = 'abc'
// RESULT (same!)
some_col | another_col | ascii_stuff
---------+-------------+------------
abc | ... | xyzzy
abc | ... | idspispopd
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment