Determine Best Database Type to Use

[kjeganathan]

Research database design and decide between relational, non-relational, and graph databases.

Deliverables:

• Type of database to use
• Pro/cons of all database types for this task
• How example dataset fits the database type + how much work it would be to make it fit

[dhsavell]

@PYee1999, @ShotaroSessions, and I worked together to research relational, non-relational and graph databases for this project.

Pros & Cons

This document contains our research, which is summarized below.

SQL/Relational

• Pros
  • Build multiple tables and databases, and formulate connections and communications between them
  • Well-known database that has many simple and concise functions
  • Can handle large amounts of transactions
  • Fast in updating, searching and querying
• Cons
  • Predefined and not flexible; table can be affected by adding and removing columns
  • Only can run on one server; expensive hardware may be needed for large amounts of data and high demand

NoSQL/Non-Relational/Document

• Pros
  • Not restricted to a predefined schema. Create new “documents” with new formats at any time
    • No need to set up a migration pipeline
  • Often JSON-like (consider BSON, MongoDB), making communication simple
    • With most languages, often just send an object instead of construct a SQL query
  • Can scale horizontally (across more, cheap servers) instead of vertically (on one, powerful server)
  • Large volumes of data and simple queries are very fast
• Cons
  • Querying capabilities are limited due to dynamic nature, especially in comparison to SQL
  • Data with arbitrary structure can lead to format errors later down the line
  • Focus on horizontal scaling may present infrastructure problems in deployed environments

Graph

• Pros
  • Superior performance when dealing with large amounts of interconnected data
    • Querying will only touch relevant data by traversing edges rather than in relational databases where you often must retrieve multiple tables
    • Query speed only depends on the number of concrete relationships rather than the amount of data
    • Can support real time updates on big data while simultaneously supporting queries because you can work on different parts of the graph at the same time
    • Good for extremely relational data like websites and interpersonal relationships because the graph models the problem closely and allows for quick retrieval.
  • Flexible Schema
    • Graph databases allow you to change schema much easier because you can simply add or drop new vertex or edge types and change their attributes. Existing queries will still work as long as there is not a direct conflict (i.e. you deleted a type of edge that this specific query traverses)
  • Extremely good for queries that involve relationships between records
    • Used for social media sites to display friends: the system only has to show you records that are directly related to yours by graph edges instead of retrieving much more data.
  • Allows for AI Infrastructure
    • Cool but overkill and likely beyond us
• Cons
  • Quite new and therefore likely harder to get into as few to none of us likely has experience with graph databases as opposed to SQL
  • Very specific use cases

Proposal: Non-Relational Database

Based on our discussion, we think a NoSQL document database like MongoDB will strike a good balance between efficiency and ease of use for our team. For us, these were the main deciding factors:

1. Simple, object-oriented interface. Most NoSQL databases have JS drivers that let you work directly with objects, eliminating the need to write SQL queries and deconstruct their responses. (An ORM solves this problem for SQL databases, but could add complexity as a separate "moving part.")
2. Good cloud support. If scaling is necessary, it's easy to acquire more resources on Google Cloud, AWS, Azure, etc.
3. Well-known tech stack. NoSQL databases are very popular in conjunction with frameworks like Express, Angular, and React (see "MERN stack"), all of which the infrastructure team are considering. This makes it easy to find relevant tutorials and manuals.

If we go through with this decision, we will have to make sure we're aware of the downsides by keeping queries simple and making our data consistent.

We did note the benefits of choosing a SQL database for this app. It would be easier to express our relationships and run queries like "most values met" and "most recognized." Additionally, our data format is mostly fixed. However, given our timeframe, scope, and stack (so far), we think NoSQL will make life a little easier.

Example Dataset

Simply getting the data in our database would be trivial. We could create collections for employees and values using the data as-is. This would only take a few lines of JavaScript to get up and running.

Realistically, we would want to make some changes. For example, we probably shouldn't store passwords in plaintext like they are presented in the data export. We would also want to think about what we want our query results should look like, and model the data based on that.

Sources

• https://www.tutorialspoint.com/Types-of-databases
• https://capgemini.github.io/design/sql-vs-nosql/
• https://resources.whitesourcesoftware.com/blog-whitesource/when-to-consider-a-nosql-vs-relational-database
• https://dzone.com/articles/what-are-the-pros-and-cons-of-using-a-graph-databa