Skip to content

Instantly share code, notes, and snippets.

@joejag

joejag/databases.md

Last active August 29, 2015 14:04
Show Gist options
  • Save joejag/1f06ccca8108acdd1180 to your computer and use it in GitHub Desktop.
Save joejag/1f06ccca8108acdd1180 to your computer and use it in GitHub Desktop.
Download ZIP
7 Databases in 7 weeks
Raw
databases.md

Relational

PostgresQL

  • PostgresQL: \h ?
  • Show indexes: \di
  • describe tables: \d+ name

Notes

  • Relational is design-first datastore. First your design the schema, then you enter conforming data.
  • foreign keys: maintaining referential integrity
  • Set Theory: TABLE, COLUMN, ROW == relation, attribute, tuple
  • These dbs are built on top of relational algrebra, or tuple relational calculus
  • Thinking about tables as an array or arrays is bad, as it encourages iteration on rows, SQL is much more declaritive than that
  • INNER JOIN: set from two tables that match on key(s)
  • OUTER JOIN: set from two tables when one table mrDust always be returned, regaardless of a match
  • LEFT/RIGHT/FULL JOIN: null & mismatch allowed on left/right/both sets
  • The speed of a rDB lies in effiecient maangement of blocks of data, reducing disk reads, query optim & other techniques.
  • Index avoid full table scan
  • B-tree indexes are good for date ranges
  • Don't add a column called 'id', as it gets confusing when you are joining.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment