Database Introductions

Databases

There are two types: traditional relational database and non-relation database. Traditional relational databases have been around forever well more like 40 years or so. Relational databases also know as RDMS or SQL databases, they store data in rows and columns and join different table to complete complex queries. A join is basically where the tables are fused together based on an attribute they both share such as customer ID. See below for all the different types of joins:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Example of an Inner Join to get all the customer and suppliers from the same city.

SELECT Suppliers.SupplierName, Customers.CustomerName, Suppliers.City
FROM Suppliers
INNER JOIN Customers ON Suppliers.City = Customers.City;

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the “Suppliers” table that do not have matches in “Customers”, these Suppliers will not be shown!

So relation databases you can select data and join different tables to achieve a different result set. They are tried and tested for many decades, we know they work well. There were designed in the 1970s when computer storage was expensive compared to CPU processing power. So it’s CPU heavy which means it will struggle with the massive amount of data. Additionally, scaling can be more difficult than NO-SQL databases because you need to scale vertically, which basically means you need to add more CPU’s and RAM so you need to migrate to bigger servers.

No-SQL

No-SQL database also known as non-relation databases. These generally have four categories:

  • Key-value store (Basically a Hash-Map/Dict)
  • Graph stores
  • Column
  • Document store (generally JSON)

You should use one of these NO-SQL databases when you want your application to have super-low latency, your data is unstructured or you do not have any relation data, You only need to serialise and deserialise data (JSON, XML, YAML). You need to store a massive amount of data.

Scaling

There are two types of scaling vertical and horizontal.

Vertical Scaling is when you scale up the servers that your database or application are running on. So your basically adding more RAM and CPU to the servers that your app is deployed to. This is much simpler than horizontal scaling because your just added physical RAM and CPUs. But this doesn’t scale very well at all.

Whereas, horizontal scaling also known as scaling out, allows you to add more servers into the pool of resources that run your application. This is how AWS works. They constantly add more and more server to increase to data centres pool of resources.

These days horizontal scaling isn’t used because cloud of computer is so easy and cheap that people get the benefit of horizontal scaling when they use AWS or another cloud provider. So there really isn't any need to use vertical scaling anymore but it’s still good to know about it.

Database replication

Most apps read a lot more data than they write. Database replication has a master and slave model. This is where the master database handles all the write operations and then the multiple slave’s databases which is a clone of the master database. So when someone writes something to the master it syncs to all the slaves. When data want to be read the application reads it from one of the slave’s drives. So an example would be something like youtube, there are a lot more consumers of content than producers so slave databases allow the master database to only deal with write and therefore all the reads the slaves can handle.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Richard Price-Jones

Richard Price-Jones

Software Engineer, Interested Finance and Tech