Hands-on different database management systems

 

When we all got locked down, I needed a project to keep my brain active. (That gets important as you get older: I just had my 80th birthday).  My wife is learning Spanish and I tried writing a novel (it didn't fly). In the end I decided to do something that I was good at - software development.  I wrote a generic system to update and query a database using node.js. Test system here: http://www.sudsjs.com.

The database is accessed through a driver, which initially was written and tested for MySL, PostgreSQL and SQLite 3.  I downloaded Oracle (XE) but it turned out to be a pain to install in Ubuntu, so I put it on the backburner. 

Since then, I have implemented (or tried) various NOSQL systems. So here is a summary and a link to any blog posts about all of the databases I have used.  I will update this post as I look at different systems.

• MySQL - works like a dream, as it always has.
• PostgreSQL - likewise
• SQLite 3 - Works fine and easy to back up because the whole database is in a single file.
• Oracle XE - struggled to install on Ubuntu; got bored. Watch this space.
• MongoDB - powerful NOSQL system and by far the biggest market share.  Blog posts on this phase:
• Lost in MongoDB: Learning to Aggregate
• Handling data consistency in a Document Database
• Database design - three ways
• Google Firestore - would be superb for some applications - but not this one.
• Firestore didn't go well for me.
• Apache Cassandra. Frankly the documentation was so obscure I lost interest.  I might come back to it.
• Apache CouchDB
• I like it a lot

Watch this space for updates.

I tried CouchDB and I like it

My COVID lockdown project is a system to keep a database updated called SUDSJS. I am using it to check out different database management systems (DBMS).  The latest is CouchDB - and I like it.

Firestore didn't go well for me.

 

I decided to use my lockdown project SUDSJS.com to test out a few different NOSQL database management systems (DBMS).  

SUDSJS is written in JavaScript using the node.js server-side system and I designed it so that I could plug in different drivers for different databases. I wrote database drivers for MySQL, SQLite 3 and PostgreSQL. The first NOSQL system I tried was MongoDB, and the SUDSJS.com test site is currently running using this. The second NOSQL system I tried was Firestore from Google. 

It didn't go well.

Database design - SQL vs NOSQL examples

 

For the last 50 years there has been one and only one way of designing a database.  SQL Database management systems (DBMS) have been the uncontested standard. (A better name is 'relational' because SQL is just the name of the language used to access them, but we will stick with it.)

But things have changed in the last 10-15 years. Databases that disobey relational rules are coming into vogue.  The term NOSQL is used for these non-relational databases (the above chart is work in progress, but I have been looking at these systems.) 

What is the difference between classic databases and this new breed? 

I will use a very simple and familiar example. Students, subjects, exam papers and results.  With the new tools we have new options and I have developed three different solutions to the same problem to illustrate the new capabilities open to us. All three solutions are working on my lockdown project - SUDSjs.com using the MongoDB DBMS.

Handling data consistency in a Document Database

You know what an invoice looks like.  We have all seen thousands of them - probably too many. But invoices illustrate an interesting problem - and I am not talking about your VAT  return. I am talking about the new kids on the database block - document databases.  An example is MongoDB. These can store all the data for one invoice in a single structured record (called a 'document').   

In a traditional Relational Database, the data is held in flat tables, like big spreadsheets. Invoices will be stored in two tables, one for information about the invoice, like customer and date (call it the 'invoice header'). The second table will store all the invoice lines. One row per line. The lines will be linked back to their invoice by an invoice number.  But what happens if the invoice header disappears, but the invoice lines are still there?

Lost in MongoDB: Learning to Aggregate

I am getting to the end of stage one in writing a database driver for MongoDB to use in my Lockdown Project sudsjs.com. All has gone well until I get to totalling up a field in the database. I had to enter the wonderful world of aggregation, which is powerful function-rich and confusing. 

Why why API?

I was looking at a page on the IBM website last week when something odd happened. Half the page loaded, then there was a spinning circle (a.k.a. a throbber) for a second or so, then the rest of the page loaded. The page itself is a few paragraphs of very boring text plus a menu. So why the drama with the throbber for a page like that?