Skip to content

Latest commit

 

History

History
120 lines (82 loc) · 4.01 KB

BE_database_ADR_20220112.md

File metadata and controls

120 lines (82 loc) · 4.01 KB

TD-logo Team 2 - TD Project


  • Status: Accepted ✔️

  • Deciders:

    • Isabel Lombardi
    • Alessandro Attene
    • Domenico Cavaglieri
    • Matteo Bisicchia
    • Gabriele Diana
    • Aldo Telese

  • Last Update: 2022/01/24

Context and Problem Statement

The aim of the project is the realization of a WebApp that supports the uploading of a file with a size larger than 2 GB and with the possibility to perform queries on it, such as:

  • Sum of number of orders and turnover per product in a user-definable time period
  • Number of single customers in a user-definable time period
  • Average value of executed orders

Decision Drivers

  • MultiThread support
  • Lightweight software
  • Easy management of > 2GB
  • Availability to support simple queries
  • Easy interaction with GO
  • Easy to interact

Considered Options

  • SQLite
  • MySQL
  • PostgreSQL

Decision Outcome

We chose to use MySQL because it meets all of our requirements and does so in a simpler way than Postgres can.

Positive Consequences

  • It's a Database we already know and know how to move with.
  • Multithread support, one of our primary requirements.
  • Simple to use

Pros and Cons of the Options

SQLite

It differs from all other SQL languages since it uses a dynamic type system, meaning a value stored in a column determines its data type, and not the column’s data type

Pros 👍

  • Can be used by multithread (as long as a single database connection is not used simultaneously in two or more threads)
  • Serverless, it doesn’t require much configuration, the setup is fast and easy
  • Using the language is simple
  • Runs very fast
  • Maximum number of rows in a table is 2^64 (18446744073709551616 or 1.8e+19)
  • Maximum database size is 140 terabytes

Cons 👎

  • Primitive syntax and formatting limitations, doesn’t support as many functions
  • Doesn't provide network access (i.e. accessing it from another machine) as it is serverless
  • Not built for large-scale applications
  • Speed to query data may be reduced if you have large data set

MySQL

Pros 👍

  • Support multithread
  • Easy to use the queries
  • Superfast in fetching the data
  • Easy to learn and apply
  • Powerful and able to handle high traffic sites
  • Feature rich
  • Many security features built-in
  • Architecture: purely relational
  • MySQL has also been optimized to reduce the gap when it comes to heavy data writes.
  • If you don't expect your application to grow or are looking for a quick prototyping tool, then consider MySQL.

Cons 👎

  • Bad Documentation
  • Not as portable as SQLite
  • Reliability issues
  • Absolutely weird default settings

PostgreSQL

Pros 👍

  • Data are safe and secure
  • Support multithread
  • Good Documentation
  • Advertises itself as "the most advanced open-source relational database in the world".
  • Built to be feature-rich, extendable and standards-compliant
  • Architecture: object-relational database, offers more complex data types and allows objects to inherit properties, but it also makes working with PostgreSQL more complex.
  • It is built with extensibility, standards compliance, scalability, and data integrity in mind, sometimes at the expense of speed; for simple, read-heavy workflows, Postgres may be a worse choice than MySQL.
  • It handles concurrency better than MySQL
  • Highly extensible. It supports a number of advanced data types not available in MySQL (geometric/GIS, network address types, JSONB that can be indexed, native UUID, time zone compatible timestamps)
  • Consider PostgreSQL for any application that might grow to the enterprise environment, with complex queries and frequent write operations.

Cons 👎

  • Architecture: object-relational database, offers more complex data types and allows objects to inherit properties, but it also makes working with PostgreSQL more complex.
  • Less popular than MySQL, so fewer third-party tools are available.