How can one think of a relational schema in terms of storing it in a NoSQL database?

Question

Recently I've been wanting to experiment with NoSQL databases, especially document store ones. After reading, I still don't understand how one might model information that is contained in a relational SQL database (i.e with tables and records) in terms of documents.

For example, a music database might have a table for Artists, Albums and Issues; an Album can have one or more Artists, and an Album can one or more Issues. This is a relatively simple example.

From what I understand of the uses of documents, there will be a document for every Album, and inside that document, the "artists" key will contain the information on each Artist, and the "issues" key will contain the information on each Issue of that Album.

Doesn't this lead to a lot of data duplication? Each album by an artist will need to contain all the information about its artist(s). On the other hand, if we have a document for every Artist, and an album has five artists and ten issues, then the album information is replicated five times within the document for each Artist and the issues information is replicated ten times within the key for each Album.

I believe I'm not thinking about the storage correctly, as this seems like a very silly way to organise a database. Either NoSQL isn't suited for this kind of storage (and I should stick to SQL), or this storage can be implemented in a better way (and I'm too stupid to see how).

Would another kind (i.e not document storage) of NoSQL database be better suited? How might one organise my example schema in a NoSQL database, with minimal data duplication? Would data duplication be somehow better?

Thank you.

Answer 1

If you are talking about document stores, and you have your song database, try think about storing the lyrics of all the songs. Those can't really be stored in a relational way. Because you can't really model the words of a lyric in a relational way to the song.

What you can do however is put all the lyrics in a document store and make them search-able.

Use the right tool for the job, don't try to model relational data in a non-relational way just because NoSQL is the trend.

Answer 2

With NoSQL/Document databases, you have to think about how you're going to be querying your data. Think of it like building indexes except the indexes are your data. You can have multiple indexes that can have duplicated data. The RDBMS gives you the luxury of maintaining all the various relations, constraints and indexes so you can have the best structure to enter and especially update data. That's the beauty of a normalized database; update the Artist's date of birth and it shows up correctly in all your queries.

Normalized data come at a price and the price can be performance, but a bigger one is a fixed schema. Relational databases love a fixed schema. They build all these plans and statistics on how to access your data because it knows all the columns, their types and even a little something about the data themselves, to optimize things for you. You're going to have to handle a lot of this in your application.

Schema design can be a lot easier in NoSQL, because for each album or even track, you have all the flexibility of entering multiple single artists, band(s), orchestras, etc. In a normalized database, you're going to need to pre-plan many of these fields and run the risk of a lot of them being empty or having special tables for special types of recordings. Punk bands don't have conductors. What if you have a recording of a street performer or some historical recording and you don't even know who it is, but you have who recorded it, when and where. It won't matter. You can allow for this type of data entry and retrieval, without restructuring a table.

I mentioned in a comment that relational databases are getting better at handling large text/binary fields and even indexing them. Some are going a hybrid route and including some NoSQL. Putting a large chunk of XML with all their different data into a field is just heresy to relational purists.

Just when you think you know all there is to know about handling a database, trying running it on two servers. That's when the fun begins. NoSQL makes this a little easier. A product like Nuodb tries to offer the best of both worlds.

Answer 3

Right off the bat, I think your example will make a lot more sense if you invert your structure so that an album is a child property of a Band (which is composed of a group of artists). With document-based NoSQL solutions, I find it helpful to sketch out the classes up front, and sometimes use nested classes (in C#) so that the top-level classes represent the Document Collection types. How you nest your classes, or which classes are top-level documents kind of depends on how you are going to interact with this data. A very common approach would be for one document to reference another by its ID, and a few denormalized fields that are commonly called. Take a look at this 2min class structure I whipped up in C# for your Music Store example in the question:

// top-level document
public class Band
{
    public BandMate[] BandMembers { get; set; }
    public Album[] Albums { get; set; }

    public class BandMate
    {
        public DateTime Joined { get; set; }
        public DateTime? Left { get; set; } // nullable
        public string ArtistName { get; set; }
        public string ArtistId { get; set; }
    }

    public class Album
    {
        public string Id { get; set; }
        public string Title { get; set; }
        public DateTime ReleaseDate { get; set; }
        public Song[] Songs { get; set; }

        public class Song
        {
            public string Title { get; set; }
            public string Lyrics { get; set; }
        }
    }
}


// top-level document
public class Artist
{
    public string Name { get; set; }
    public string Id { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string[] Background { get; set; }
    public Instrument[] InstrumentsKnown { get; set; }
    // more fields here for divorces, children, overdoses, etc....
}

Some objects, like Songs, are contained completely within the parent class, so they are entirely part of the larger "Band" document/class. However, the Artists themselves might contain lots of lots of information that isn't 100% necessary when rendering Album information, which probably just cares about their names, or maybe their instruments. So the BandMate class contains the Artist ID, in case you want to do lookups on the Artist document for that full info, but also includes their denormalized Name, so that it is very easy to render the membership of the band from a simple query on Album. This level of denormalization is up to you, and constrained by the flavor of Document NoSQL database you choose.

Finally, without trying to sound condescending, if you aren't familiar with a paradigm like DocumentDb/NoSQL, then its pretty easy to say "Oh well I guess MY apps data is purely relational," when really its just that you've been using relational DB's to store 99% of application data for years since that's whats most commonly available (prior to this wonderful new era of MongoDb/CouchDb/RavenDb/etc). This example doesn't really show the full power of schema-less storage, but in C# for example, I can store child object collections under the label of an interface, and the NoSQL db can do the work of initializing them to their original type, without me having to care about setting up diff tables and JOINing all that stuff together.