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?

  1. Multiple documents, one for each ingredient
  2. One document per reference

    { "_id" = "ingredients", "ingredient": [{ "name":"Apple", ... }] }

  3. One document with all references

    { "_id" = "references", "ingredients": [{"id": "apple", "name": "Apple", ...}], "spices": [{"id": "cinnamon", "name": "Cinnamon", ...}] }

  • Nice question about data layout in collections. Please get the word out for more questions like this in the DBA.SE. +1 !!! – RolandoMySQLDBA Sep 23 '11 at 17:00

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.

| improve this answer | |

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.