13

I am trying to figure out how to best implement this for my system...and get my head out of the RDBMS space for now...

A part of my current DB has three tables: Show, ShowEntry, and Entry. Basically ShowEntry is a many-to-many joining table between Show and Entry. In my RDBMS thinking it's quite logical since any changes to Show details can be done in one place, and the same with Entry.

What's the best way to reflect this in a document-based storage? I'm sure there is no one way of doing this but I can't help but think if document-based storage is appropriate for this case at all.

FYI, I am currently considering implementing RavenDB. While discussions on general NoSQL design will be good a more RavenDB focused one will be fantastic!

Thanks, D.

Alex Klaus
  • 8,168
  • 8
  • 71
  • 87
codedog
  • 2,488
  • 9
  • 38
  • 67

3 Answers3

24

When modelling a many-to-many relationship in a document database, you usually store a collection of foreign keys in just one of the documents. The document you choose largely depends on the direction you intend to traverse the relationship. Traversing it one way is trivial, traversing it the other way requires an index.

Take the shopping basket example. It's more important to know exactly which items are in a particular basket than which baskets contain a particular item. Since we're usually following the relationship in the basket-to-item direction, it makes more sense to store item IDs in a basket than it does to store basket IDs in an item.

You can still traverse the relationship in the opposite direction (e.g. find baskets containing a particular item) by using an index, but the index will be updated in the background so it won't always be 100% accurate. (You can wait for the index to become accurate with WaitForNonStaleResults, but that delay will show in your UI.)

If you require immediate 100% accuracy in both directions, you can store foreign keys in both documents, but your application will have to update two documents whenever a relationship is created or destroyed.

Richard Poole
  • 3,946
  • 23
  • 29
  • 1
    Thanks - great explanation. I have gone with a combination of storing foreign keys on each side, as well as one side only for some relationship. The frequency of my UI accessing the relationship in each direction is pretty much the key. – codedog Jan 30 '11 at 23:46
7

This went a long way towards solving my question!

codedog
  • 2,488
  • 9
  • 38
  • 67
1

Answer to the question

Many-to-many relationships in NoSQL are implemented via an array of references on one of the entities. You've got two options:

  1. Show has an array of Entry items;
  2. Entry has an array of Shows.

Location of the array is determined by the most common direction of querying. To resolve records in the other direction - index the array (in RavenDB it works like a charm).

Usually, having two arrays on both entities pointing to each other brings more grief than joy. You're losing the single source of truth in an eventually consistent environment... it has potential for breaking data integrity.

Check out this article - Entity Relationships in NoSQL (one-to-many, many-to-many). It covers entity relationships from various angles, taking into account performance, operational costs, time/costs of development and maintenance... and provides examples for RavenDB.

Alex Klaus
  • 8,168
  • 8
  • 71
  • 87