Table of contents

Child pages

• 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()