1đź‘Ť
While over normalizing imposes serious problems, you have under normalized your schema.
- It does not scale well. As of the time of this writing, there is a 16MB size limit on BSON documents. If you have a very large vendor, you (theoretically) might run into problems. Imagine WalMart with thousands of shelves in different locations. Remember Facebook has to pays huge amounts of money because they vastly underestimated the need to scale.
- With your current schema, you have to have an arbitrary number of indices when you want to index all shelves. Setting aside other problems: building an index isn’t free, even when done in background.
- Only one index will be utilized per query. So we need to reduce the number of indices.
- The questions you are asking don’t even need this schema. Both of the time, the vendor is known. So you can easily use a more conventional approach, with easy and efficient queries.
Here is how I would do it. I’d have a vendor schema with stuff like name and location. Next, I’d have a shelf schema. Each shelf will have a reference to a vendor, like in SQL. The only thing is that those references are “weak”, so to say. But since the vendor is known, so is his _id
to query the shelf schema.
The vendor schema
This is pretty straightforward
{
'_id': new ObjectId(),
'name': 'Acme Mart',
'location': {
type: 'Point',
coordinates: [ 34.180278, -118.328333 ]
}
}
The shelf schema
Actually it is quite easy, too
{
_id: new ObjectId(),
vendor: "idOfVendor",
description: "Shelf 14"
contents: ["Apples", "Oranges", "Kiwi" ]
}
The indices
Setting aside the geospatial index we would need for the vendors location
field, here are the indices you need
// Only if you want to search by name
db.vendors.ensureIndex({name:1})
// we use a compound index here
db.shelves.ensureIndex({vendor:1,contents:1})
You could even use a text index on contents
, enabling the search to find both “apples” and “apple” to be found via “apples”, but that’s on you to decide.
Your queries
Since the vendor is known, and thus his _id
, we can easily find all shelves containing Kiwi:
db.shelves.find({vendor:"idOfVendor", contents: "Kiwi"})
Counting the number of shelves becomes even simpler:
db.shelves.find({vendor:"idOfVendor"}).count()