Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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", ...}] }

share|improve this question
    
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
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
That was my choice as well. Thanks –  Patrick Sep 27 '11 at 8:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.