<div class="statcounter"><a title="tumblr counter" href="http://statcounter.com/tumblr/" target="_blank"><img class="statcounter" src="http://c.statcounter.com/6284484/0/73b30f93/1/" alt="tumblr counter"></a></div>

NoSQL: Master/Detail sample

Recently I gave a speech about the NoSQL subject and one of the main concerns was: “How can I manage my relationships if NoSQL is not relational?”, also I found that this is a very common question and one of the key scary parts when you’re approaching to the NoSQL Document world. (take a look at stackoverflow and you will see what I’m talking about)

To address this I will explain how to model a “typical” enterprise application using documents and how to approach the design of your model using djondb.

The problem:

I want to create a receipt system using NoSQL: In other words you want a system to your restaurant that will handle all the purchases made saving the receipts (taxes issues comes to my mind), but you want to be able to get some “reports” or any kind of tracking system to know how much you sold, what is the favorite food, etc.

The RDBMS approach

I know every architect could get a different solution for this problem, but I will go to the “easier” to understand approach, a master/detail tables with a form screen that will capture the receipt and save it to these master/detail tables. Does it sound familiar?

First step model the database:

This two tables will hold our receipts, to get a workable sample you will need the Customer and Products tables, so let’s add them:

Now that you’ve your data model is time to build up your application, the implementation will be heavily influenced by the development language, but this will look like this:

Until now, the life was good with us, everything looks clean and easy, the DB is created the screen looks nice, etc. But how will this work on the server side? Lets assume the following workflow:

  • The user enters to the screen, the date is automatically filled.
  • He needs to select the customer, so we create a new screen that pop ups up and do some lookup to select the customer
  • Now that the user wants to add items we have two choices (since we’re on web the decisions are different from a desktop application):
    • First: Save the “master” record and then start to add items to it
    • Second: Add the items into memory (browser or session variables) and store everything at “checkout” button

    No matter which one is your choice, or any other choice, your code will do the following instructions:

    • Insert into Master Values (’2012/10/10′, 7172772) (let’s assume the client id is: 7172772)
    • Insert into Detail Values (10, 10012, 1, 350) (iPhone code is 10012)
    • Insert into Detail Values (10, 10015, 2, 3) (Cable code is 10015)

    If you’re using jQuery or any other UI framework the chances are high that you’re using json as interchange format, if not you will just get each value, one by one and format it to fill some parameters into the SQL statement.

  • Ufff… almost done, now you will need to show the receipt to the user (readonly mode), at this moment you’re confident with your SQL statements and you create the following:

    SELECT … From MasterDetail md inner join Products p on p.id = md.prodid where idCustomer = 7172772

    This will retrieve the details element, but you will need another select to retrieve the header of the receipt, so you go and write on some code:

    SELECT … FROM Master m inner join Customer c on c.id = m.customerid where c.id = 7172772

    Then you use your backend code to format this into some jsons and return it to be rendered by the framework at the browser side.

Ok, this was the process to create and model a typical RDBMS solution, you could argue that you can use ORM (EJB, Hibernate, etc) or something like this, but at the end you will just need more classes, more code and more “pain”.

Drawbacks
The solutions looks awesome, it’s easy and straightforward, but 2 months later you realize that there’s a new model of iPhone and the one you create could be misleading because the name does not states that is the iPhone 3 and not the iPhone 4S, you need to “update” the old product record to change the name of the product, this is a trivial task, just create an update sql and that’s it, right? BUT… what if a customer comes later and want to reprint his receipt? you cannot change the product name, the receipt should be exactly as it was on the time of the sell, right? (denormalization comes to save the day, and you will need to change some SQL instructions, some code at server side, the screen, darn, you will need to change everything)

NoSQL solution

The same problem from the NoSQL side, it’ll be something like this:

  • Although you don’t need to create the tables, databases, etc in order to use them, it’s wise to model your data in advance, this will avoid some typical mistakes. The browser will send the following json as soon as the user press the “check” button:
    
    {
    "date": "2012/10/10",
    "Customer": { "id": 7172772, "name": "John Smith"},
    "details": [
    {"productId": 10012,
    "description": "iPhone",
    "units": 1,
    "price": 350},
    {"productId": 10015,
    "description": "USB Cable",
    "units": 2,
    "price": 3}
    ]
    }
    

    Take a look how I started denormalizing the data from the beginning, this will avoid extra queries and will solve the “drawback” problem stated before.

  • At the server side you will just store the document into djondb using something like:
    
    $c = new Connection();
    $c->connect("localhost");
    $data = $_POST['data'];
    $receipt = json_encode($data);
    $c->insert('testdb', 'receipt', $receipt);

    I didn’t need to breakup the incoming parameters or create any SQL statement, just send the record to the database, take a closer look I’m saving the product’s name and customer’s name, this will avoid the problem mentioned at “drawbacks” from the RDBMS solution (you could change the RDBMS too, but this will just add more and more text to this already long post). If you want to do some checks at the server side you could use the JSON as is, and just do the validations on the serverside (most of the languages already has some JSON framework).

  • Now, how do you retrieve the data from the server and draw it as readonly. Easy… first the server side code:
    
    $c = new Connection();
    $c->connect("localhost");
    $id = $_GET['customerid'];
    $filter = '{ customerid: "'.$id.'"}';
    $jsonResult = $c->findByFilter('testdb', 'receipt', $filter);

    Here you will get the document from djondb and sent it as is to the user interface and render the data using jquery is a trivial task, you will not need to retrieve each part of the receipt (master/detail) separated just one sentence and that’s it.

One of the beautiful things of jQuery is that you can manage the screen on memory and send the full json to the server once the user press the “check” button, you can add records and do some ajax to return the products name, customers, etc and keep the master/detail model into a json variable at the browser side and send a single process request to the server to save the full receipt, using NoSQL you could store this straighforward to the DB, but at the RDBMS you will have some problems to split the information before you can store it to the database, even if you use a ORM mapping framework you will end up with more code that in the NoSQL world.

Updating the product’s name

As I said before what if the product’s name changes and I want to keep the receipts as they are at the moment of the creation, using the denormalized approach this will not have any problem, just do a update on the products table (RDBMS model) or at the document in djondb.

Adding new fields to the model

The next typical problem is related with the new fields, how will I handle this will be my next post, so keep up.

5 Responses to “NoSQL: Master/Detail sample”

  1. Antonio says:

    “Connection” is a really bad name for a class provided by a third-party vendor. Adding a prefix would be good. Djondb_Connection or even better Djondb\Connection

  2. Andrey says:

    The post does not really tell anything. Why would you think about de-normalization from the very beginning in the second example, and not think about it in the first one? If we do this de-normalization with RDBMS, there is no difference. I do not see any advantage in the second example.

    • cross says:

      Hi Andrey, sorry for the delay on the response, although the RDBMS sample and NoSQL look very similar, please take note that in NoSQL we store the details along with the header, they’re different approaches, the purpose of this article is to show the differences between the approaches and not to lead the users to uninstall their RDBMS systems.
      It’s true that you can achieve similar things on a RDBMS doing de-normalization, but there’re other differences as well, for example you can create new “fields” on the fly, without doing drop/create/alter tables, you don’t even need to update your previous records.
      I encourage you to read the other articles to check out how NoSQL improves the development agility and helps developers to create great applications with minimal efforts.

Leave a Reply