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.


1. Classic design.

Relational databases expect you to 'Normalize' the data. The data is stored in tables, and in this example, there is a subject table and a papers table. Each paper has a link up to the subject so in this listing of the Maths subject the program can find and include all of the papers.  

Click on 'New' to add papers.  Click on the three dots next to a paper and you can see the link back to the subject.  There is a list on this page of the results, so you can navigate around this section of the database easily.  

2. Denormalized design

Now normalisation is not a natural approach. You would get out a piece of paper, write the subject name and then add the papers under it as a list. But classic SQL systems don't do lists in a record. Not of indefinite length anyway.  NOSQL systems do. 

So this opens up a different and much more intuitive way of storing this data.   In this example the subject has the papers as a list, rather than in a separate file. Click edit to see how papers are added.  

Taking the technique further, the student records have a list of the subjects studied, and for each subject a list of papers and scores. So, there is a table of subjects and papers for each student. 

3. Variable data

Another problem with SQL systems is that they assume that every record is the same. But life is not always like that. Products have different specifications. For example, electrical products have different specifications to clothing.

NOSQL allows you to put different data into records. I dealt with this in SUDSjs.com by defining groups of fields and specifying different groups for different record types. 

I used this feature in this student example where each subject is treated as a field group with a field for each paper. This feature is at the proof-of-concept stage but is promising. A more realistic example is the test product file, where the product group is used to create the extra specification fields.  They appear under the 'other' tab.

The home page for the three examples is here (click on 'Admin page'.)

Should we use the new features?

Just because a gadget is available doesn't mean we should use it. If your environment already has a NOSQL database system, it doesn't mean you can't design a classic normalised data structure. 

I will talk about the benefits of each approach in another blog entry.

The code for this system is here.


No comments:

Post a Comment