Table of contents

• http://coding-geek.com/how-databases-work/
  • Highly rec'd by soham

• https://www.quora.com/Why-doesnt-Quora-use-PostgreSQL
• https://www.quora.com/Why-does-Quora-use-MySQL-as-the-data-store-instead-of-NoSQLs-such-as-Cassandra-MongoDB-or-CouchDB
• Typical Programmer - Why Programmers Don’t Like Relational Databases
• Typical Programmer - Abject-Oriented Databases
• 2014.02.21 - DigitalOcean - SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems - I thought this was a helpful overview.

Tools

• SQLiteBrowser
  • I've used this and it works fine.

Best practices

• Enum data types
  • Use reference tables
    • http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/
    • IIRC this is what was done at ServiceFusion

Specific DBs

MySQL

• How to get your database to allow emojis and other unusual characters:
  • ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

SQLite

• Installation
  • Go to SQLite3 download page
    1. Go down to the “Precompiled Binaries For Windows” section.
    2. Download “sqlite-shell” and “sqlite-dll” archive files.
       • sqlite-dll-win64-x64-3160200.zip
    3. Download the sqlite tools .zip
       • sqlite-tools-win32-x86-3160200.zip
    4. Unpack everything in C:\sqlite folder.
    5. Add that folder to your PATH.
• Creating the empty .db file:
  1. Hit the Windows key, type 'cmd', then right-click the 'Command Prompt' result and select 'Run as Administrator'.
  2. Navigate to the folder where you want the db to be.
  3. Type 'sqlite3 name_of_the_db.db' and hit 'Enter'.
• Simple example of a schema file:

  • 
    

    CREATE TABLE salespeople ( id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, commission_rate REAL NOT NULL );

    
    

Good to know

• SQLite will allow you to insert a string into an integer field. (Source)

MongoDB

Using MongoDB:

• Import a bson file into your Mongo /data/ folder: mongorestore -d db_name -c collection_name "C:\path\to\export.bson"
  • Make sure to add your MongoDB's 'bin' folder to the PATH to get mongorestore to work from a command prompt.
• Run Mongod: "C:\Program Files\MongoDB\Server\4.0\bin\mongod.exe" --dbpath="c:\data\db"
• Run MongoDB shell: "C:\Program Files\MongoDB\Server\4.0\bin\mongo.exe"
• Show list of dbs: 'show dbs'
• Switch to using a particular db: 'use <db>'
• Show list of collections for the active db: 'show collections'

Queries

• Get all records from a particular collection once you're switched to a particular db
  • 'db.<collection_name>.find()'
• Check if a field contains a string
  • db.getCollection('collection_name').find({"Party1": {$regex : ".*similarly situated.*"}})
• Find records by non-existence of a field:
  • db.mycollection.find({ "price" : { "$exists" : false } })
• Find a record by its id:
  • db.collection_name.find({"_id": ObjectId("587862a88362593254464c69")})
    • So you can find it by the id's string value, but you need to wrap the string with ObjectId()

Neo4j

General

• Neo4j Blog

Tutorials

• Neo4j GraphAcademy
  
  • There's a lot of stuff here
• Neo4j - The Definitive Guide to Graph Databases for RDBMS Developers
• 2015.09 - O'Reilly - Nicole White - Building Web Apps Using Flask and Neo4j

Tools

• Cycli - A Command Line Interface for Cypher.

Pros and cons of graph databases

• Summary
  • It seems like Neo4j is helpful for making join-heavy queries orders-of-magnitude faster.
• Links
  • http://stackoverflow.com/questions/19570654/do-graph-databases-deprecate-relational-databases
  • https://groups.google.com/forum/#!topic/neo4j/mts6H9Py-2I
  • https://neo4j.com/developer/graph-db-vs-rdbms/
    
    • "If you use many-to-many relationships, you have to introduce a JOIN table (or junction table) that holds foreign keys of both participating tables which further increases join operation costs. Those costly join operations are usually addressed by denormalising data to reduce the number of joins necessary."
    • "This ability of pre-materializing relationships into database structures allows Neo4j to provide performances of several orders of magnitude, especially for join heavy queries, the minutes to milliseconds advantage that many users leverage."

PostgreSQL

Websites

• Postgres OnLine Journal

Tutorials

• PostgreSQL Exercises (pgexercises.com)
  • This looks good.

Extensions

• PostGIS
  • This was one of the major reasons Krieger and Systrom went with Postgres over MySQL. (Source)

Books

• Lists
  • PostgreSQL.org - Books
  • Postgres OnLine Journal - Book Store
• Individual books
  • PostgreSQL.org - PostgreSQL 9.6.1 Documentation ← This is basically a book.
  • Beginner-level
    
    • 2001 - Beginning Databases with PostgreSQL (4/5, only 11 reviews)
    • 2005 - Beginning Databases with PostgreSQL: From Novice to Professional (5/5)
    • 2002 - Practical PostgreSQL (2.5/5, 17 reviews)
  • Intermediate-level
    • 2001 - PostgreSQL (Linux) (3/5, 5 reviews)
    • 2001 - PostgreSQL Developer's Handbook (4/5)
    • 2005 - PostgreSQL (2nd Edition) (4/5, 22 reviews)
    • 2010 - PostgreSQL 9.0 High Performance (4.5/5)
    • 2014 - PostgreSQL: Up and Running (2nd ed) (3.5/5, 15 reviews)
    • 2014 - PostgreSQL 9 High Availability Cookbook (5/5, 6 reviews)
    • 2015 - PostgreSQL Server Programming - Second Edition (4.5/5, 5 reviews)
    • 2015 - PostgreSQL Developer's Guide (3.5/5, 4 reviews)
    • 2015 - PostgreSQL 9 Administration Cookbook (2nd ed) (4.5/5, 2 reviews)
    • 2015 - PostgreSQL for Data Architects (5/5, 2 reviews)
    • 2015 - Troubleshooting PostgreSQL (5/5, 4 reviews)
    • 2015 - Learning PostgreSQL (4/5, 5 reviews)

Snippets

• Get the number of rows in all tables in a database.

  • SELECT schemaname,relname,n_live_tup
    FROM pg_stat_user_tables
    ORDER BY n_live_tup DESC;