How to handle "reference tables" in CouchDB? What's the best way to handle "reference tables", tables that contain static data, for example a list with various kinds of ingredients for food?


Multiple documents, one for each ingredient
One document per reference

{
  "_id" = "ingredients",
  "ingredient": [{ "name":"Apple", ... }]
}
One document with all references

{ "_id" = "references", "ingredients": [{"id": "apple", "name": "Apple", ...}], "spices": [{"id": "cinnamon", "name": "Cinnamon", ...}]
}
As a MySQL DBA staring at this, I think in relational terms. I also think in terms of doing JOINs. However, from a NoSQL standpoint, JOINs would have to be done programmatically.

Multiple documents, one for each ingredient : This will make smallest amount of static data to place in collections. Yet, you should be prepared for getting references, building temporary collections for yourself with CouchDB or sending data straight to your app.

One document with all references : If you are content with mining Collections out of Collections out of Collections ... (inifinity), you are willing to construct such collections, and you are comfortable with it, then storing the data this way would be OK. Please just make sure you are not the only CouchDB Jedi around (unless it's a matter of job security), since you might want your code understandable and maintainable by others.

One document per reference : Now, this is a nice middle ground. It will provide some flexibility in the event there are changes in the Collection layout. Simple retrievals for ingredients can be independent of retrievals for spices or utensils. Performing programmatic JOINs is still needed but not all that deep. Those who are a novice to NoSQL paradigms (like myself) would be comfortable understanding and retrieving data without the fear of breaking deeply-entrenched complex data representations.

My personal choice would be option #2.