Hi, I was wondering if there are any general or specific rules/principles that guide your decisions to use Document Oriented Databases like CouchDB over Relational Databases like MySQL. There is no information on when to specifically, down to the table-names or showing an example schema/site, that I am aware of on the Internet... I am using Merb and DataMapper and there is talk about using both CouchDB and MySQL together in a single application, with MySQL being used for more static entities like user accounts and contact info and CouchDB being used for more dynamic entities like images or uploads or things that have variable amounts and types of properties, but it's unclear. What's the best way/pattern to separate out the roles each database plays in an application? If, for example, you wanted to create a crazy website with social networking, eCommerce, job posting, instant messaging, etc., there would be an enormous amount of different entities/objects in the database all relating to each other (assets = photos, songs, videos, blogposts…, others = users, orders, profiles, products, tags…), and if you had everything taggable, authenticated, and commentable, then that would add a lot of interconnectivity between the tables/entities/objects/classes. So I was thinking how/when you should choose MySQL and how/when you should choose CouchDB. I haven't been able to find anything on the internet outlining this clearly. I'm sure it can be outlined well though.
Maybe it could be such that: -MySQL handles entities that have 1) static properties (columns of tables are pretty much constant, like a "user_account" or "contact_address" are…), and 2) static (username) or dynamic (chunk of text) values. -CouchDB handles things that have 1) dynamic properties (columns in relational database become "keys" or whatever in Couch, and these keys aren't fixed but can change from resource to resource), and 2) dynamic values only. And 3) Couch would handle all uploaded/attachment data because many documents, even if they are the same type (image, video, word…), might have different properties depending on the situation. I've just been trying to think how you could manage 10s or 100s of potential "entities/tables" in a database and wanted to pass it your way to get your input. If CouchDB handled things that only had "dynamic" properties, then an "image" entity for example could have [title, caption, size, attachment_fu.stuff] in one case and [title, tags] in another, or whatever; there is no need to have a single "Images" table with a specified set of properties (rows). Image properties or rows can instead be dynamic. But what entities do we choose to be dynamic? So instead of having single inheritance in relational databases and huge table with tons of null columns, you could have, basically, a table-per-asset, or a document--that's what Couch is. But what kind of structure do you apply to Couch documents? And what entity should be placed in Couch vs. MySQL? Maybe all file-uploads can go to Couch and all structured-mostly-unchanging entities, like user accounts, can go to MySQL? But then, is it easy to connect data-objects between two distinct databases? Like if a user (from MySQL) has_many images (from CouchDB), would doing the 'finds' be expensive or anything? Just some thoughts on how and when to use Couch vs. MySQL. Any answers to this question would really help out a ton. Best, Lance
