From SQL to NoSQL


#1

I’d like to get some guidance (reference books and sites) to help “unlearn” the relational db constraints while having great performance on NoSQL.

I’ll introduce my current issue:

I have several products and several users, there’s a N-to-M relationship between those entities.
Easily I could move it to NoSQL having a users bucket with this contents:

// user
{
    id: "user::35c8a035-ee7e-48b8-b81b-6752ab642120",
    name: "enwine"
    products: [
        "product::a251ca83-c95c-491a-be11-00d5605e75a4",
        "product::3773a25a-5452-4891-ada0-bb7bb1747bc9",
    ],
    ...
}

And another bucket with Products like:

// product
{
    id: "product::a251ca83-c95c-491a-be11-00d5605e75a4",
    name: "VIP User"
    price: "$20/mo"
}

The point is that I want to be able to:

  1. List all users (including more fields than just the id) that own a given product. I figured out this solution for that: Create a view that from the Users bucket that returns all users for a given product id.
  2. List all products (including more fields than just the id) owned by a given user. This is more complex, as it is just user.products, but to display the list with more info, I’ll have to fetch all of the products of the list, one by one.
  3. Being able to automatically remove one product from all users using it on demand: I mean, if one product (let’s say “VIP Membership”) gets removed, users can no longer own it, and therefore, the only way I can figure out to do it, is updating users document one by one, which is not admisible.

Could anyone please enlighten me?

Thanks,

~enwine


#2

Hi,

First, you should see a bucket more like a database than a table. It’s easier to have products and users in the same bucket.

Here are some links about Data Modeling that should help you:

http://blog.couchbase.com/three-things-know-about-document-database-modelling-part-1
http://blog.couchbase.com/data-modelling-when-embed-or-refer
http://blog.couchbase.com/manual-secondary-indexes
http://blog.couchbase.com/data-modelling-key-design

  1. Indeed creating a view is a solution.
  2. To fetch all the products of a user, you indeed have to fetch every products using their Ids from the user.products field. It’s easy to do a bulk get with our SDKs. Remember that doing a key/value get is fast. Don’t be afraid to hit the database with this. It’s not a query.
  3. Unfortunately when you start denormalizing data, this becomes something you actually have to do. In the future you might be able to do this with a N1QL query. But right now you do have to update the Users.