1

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.

  • What NoSQL database?There are lots and lots of different technologies with completely different philosophies commonly grouped under the huge "NoSQL" umbrella. Depending on which one you use your schema would look completely different. – Philipp Apr 29 '16 at 11:42
  • 1
    youtu.be/qI_g07C_Q5I?t=15m1s – Euphoric Apr 29 '16 at 11:54
  • Why do you think data duplication is bad? Storage is cheap. Document indexing is fast. – Euphoric Apr 29 '16 at 11:56
  • 1
    @Euphoric Wouldn't there be a lot to update if I needed to change one detail about an Artist, such as the date of birth? I would have to update every Album document with this new information. With a lot of information, it adds up, and I'm having a hard time imagining that just because storage is cheap, that I should use it. Isn't data repetition more prone to errors, too? – q3d Apr 29 '16 at 12:01
  • 1
    Creating the possibility of inconsistent data is by far the biggest risk of deliberately duplicating data. The extra storage space or the performance cost of multiple updates are almost inconsequential compared to that. – Ixrec May 1 '16 at 21:00
2

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.

  • 1
    +1 for "..don't try to... just because NoSQL is the trend." Although in the example about lyrics I'd say you can store them in a relational database. – Tulains Córdova Apr 29 '16 at 13:07
  • @TulainsCórdova - You could store lyrics in a relational database (that's not such a bad example because they're not as long as an epic poem) and many of them are doing a better job handling and indexing blobs, xml and even filed documents, but that doesn't mean you should just because you can. – JeffO Apr 29 '16 at 13:32
  • @JeffO I always see binary and text blobs in databases as a way of storing non-relational data in a relational database. But things like a full text search feature or even the keyword "LIKE" in SQL databases shows the need that relational modeling isn't always the best. – Pieter B Apr 29 '16 at 14:29
  • 1
    Because you can't really model the words of a lyric in a relational way to the song. Nope, absolutely not. Afterall, it's not like "full text search" is a thing that's existed for years and years in SQL databases. Nope, just a pipe dream, that... – Mason Wheeler Apr 29 '16 at 20:52
  • @MasonWheeler it's just an example about modeling. Actually full text search is the NoSQL way of indexing large amounts of text in an SQL database. See, even classic SQL databases have a bit of NoSQL. – Pieter B Apr 30 '16 at 6:06
1

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.

0

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.

  • What if there's more than one band on an album? Seems more of a many to many relationships – JeffO Apr 29 '16 at 20:08
  • If every entity, or the majority of all the entities, need to be related in many-to-many that way, then you could just store each type (Song, Album, Artist, Band, etc) as a separate top-level document, with IDs and crucial denormalized fields in each for the relationships. At that point you MIGHT start watering down the benefits of a document database though, as opposed to using a relational database. Doc Databases still give you lots of benefits (scalability, fast reads, better full-text searching) if you are willing to enforce their weak referential integrity in the app level. – Graham Apr 29 '16 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.