Wrapping my head around MongoDB, mongomapper and joins...

Question

I'm new to MongoDB and I've used RDBMS for years.

Anyway, let's say I have the following collections:

Realtors
 many :bookmarks
 key :name


Houses
 key :address, String
 key :bathrooms, Integer


Properties
 key :address, String
 key :landtype, String


Bookmark
 key :notes

I want a Realtor to be able to bookmark a House and/or a Property. Notice that Houses and Properties are stand-alone and have no idea about Realtors or Bookmarks. I want the Bookmark to be sort of like a "join table" in MySQL.

The Houses/Properties come from a different source so they can't be modified.

I would like to be able to do this in Rails:

r = Realtor.first r.bookmarks would give me:

House1 House2 PropertyABC PropertyOO1 etc...

There will be thousands of Houses and Properties.

I realize that this is what RDBMS were made for. But there are several reasons why I am using MongoDB so I would like to make this work.

Any suggestions on how to do something like this would be appreciated.

Thanks!

Answer 1

OK, first things first. You've structured your data as if this were an RDBMS. You've even run off and created a "join table" as if such a thing were useful in Mongo.

The short answer to your question is that you're probably going to have re-define "first" to load the given "Bookmarks". Either "server-side" with an $in clause or "client-side" with a big for loop.

So two Big Questions about the data:

1. If Bookmarks completely belong to a Realtor, why are they in their own collection?
2. If Realtors can Bookmark Houses and Property, then why are these in different collections? Isn't this needless complication? If you want something like Realtor.first on bookmarks why put them in different collections?

The Realtors collection should probably be composed of items that look like this:

{"name":"John", "bookmarks": [ 
   {"h":"House1","notes":[{"Nice location","High Ask"}] },
   {"p":"PropertyABC","notes":[{"Haunted"}] }
] }

Note how I've differentiated "h" and "p" for ID of the house and ID of the property? If you take my next suggestion you won't need even that.

Taking this one step further, you probably want Houses and Properties in the same collection, say "Locations". In the "Locations" collection, you're just going to stuff all Houses and Properties and mark them with "type":"house" or "type":"property". Then you'll index on the "type" field.

Why? Because now when you write the "first" method, your query is pretty easy. All you do is loop through "bookmarks" and grab the appropriate key ("House1", "PropertyABC") from the "Locations" collection. Paging is straight forward, you query for 10 items and then return.

I know that at some level it seems kind of lame."Why am I writing a for loop to grab data? I tried to stop doing that 15 years ago!" But Mongo is a "document-oriented" store, so it's optimized for loading individual documents. You're trying to load a bunch of documents, so you have to jump through this little hoop.

Fortunately, it's not all bad. Mongo is really fast at loading individual docs. Running a query to fetch 10 items at once is still going to be very quick.