new couch user: "denormalizing" using views? General application architecture suggestions?

[Michael Kohout <mw...@gmail.com>]

Hi All-

So, I'm doing a spike on what it would take to convert a portion of an
existing rdbms-backed system to something that uses couch instead.  I'm also
pretty green with couch.

Because this would be a conversion from a relational system, the way I
envision the system working is that it will take row-like documents as
input, then, at some point inside couchDB these things will be denormalized
into documents to be reduced.

The way I see it, there is one, possibly two ways to do this:
1)  through the _changes feed.  This seems like it's the most couchy way to
do things.  However, for this application it also seems like it's the most
complex way to implement-the current system has a pretty large number of
normalized, interdependent tables and I'd like to not force a specific order
for things to be loaded into couch(I don't want to force the system to work
via a batch load-ideally, it'll be driven by user input via a browser).  So,
I'd essentially have to handle all places where a "row" is used when
denormalizing via _changes.

2)  via a view(IF there is a way to mark other documents as part of a view).
 It looks like, at least if I use an external view server, I could build the
denormalized docs during the map which'll be processed during the reduce.
 This would be super simple(in my opinion) for implementation, but it
doesn't seem like couch offers functionality for generating multi-document
views(while also hooking into couch's view-invalidation mechanism, so that
if one of the docs that make up this view is changed, the view will be
regenerated).  Am I wrong?  Is this something that is planned?


What does the couch community think of this?  Is there some other pattern
that might result in a simpler system?

Thanks for any suggestions you might have, and if I haven't been clear about
what I want to do, please feel free to ping me on it.

Mike Kohout

Re: new couch user: "denormalizing" using views? General application architecture suggestions?

[Robert Johnson <ro...@rowanshire.net>]

Michael

Couch is very different from a RDBMS. From what you have said, I think that you want to load your CouchDB "one document at a time" as users access them in a browser session. 

Before loading anything you first need to design your documents. To do this you need to define the data in your database tables as "documents" as opposed to related tables. For example, a sales invoice in RDBMS terms would have order header table and order details table with a one to many relationship between them. In Couch you would have a single document for each order which would have all the order header data in it and then multiple order lines all in the same document. If you were to express it as a PHP stdObject you would have something like

Order_ref
Order_date
Order_delivery_address
order_lines[0]->item_code
order_lines[0]->item_desc
order_lines[0]->item_price
order_lines[1]->item_code
order_lines[1]->item_desc
order_lines[1]->item_price

Couch will let you store the above object as a document and retrieve it via keys.

To retrieve the documents via keys, you would build couch views with the relevant key/value pairs (e.g. order_ref)

Once you have done the design you can load document by document by altering your application to assemble a document from your RDBMS when a user asks to see it and saving the document to couch. One intricacy would be to know which documents were saved in couch and which not. You could either alter your existing app to store the couch document id in the RDBMS which you could check for or you could try and retrieve from Couch first and if you fail to find it you could go to your RDBMS

Hope this helps

Bob

On 7 Feb 2011, at 17:14, Michael Kohout wrote:

> Hi All-
> 
> So, I'm doing a spike on what it would take to convert a portion of an
> existing rdbms-backed system to something that uses couch instead.  I'm also
> pretty green with couch.
> 
> Because this would be a conversion from a relational system, the way I
> envision the system working is that it will take row-like documents as
> input, then, at some point inside couchDB these things will be denormalized
> into documents to be reduced.
> 
> The way I see it, there is one, possibly two ways to do this:
> 1)  through the _changes feed.  This seems like it's the most couchy way to
> do things.  However, for this application it also seems like it's the most
> complex way to implement-the current system has a pretty large number of
> normalized, interdependent tables and I'd like to not force a specific order
> for things to be loaded into couch(I don't want to force the system to work
> via a batch load-ideally, it'll be driven by user input via a browser).  So,
> I'd essentially have to handle all places where a "row" is used when
> denormalizing via _changes.
> 
> 2)  via a view(IF there is a way to mark other documents as part of a view).
> It looks like, at least if I use an external view server, I could build the
> denormalized docs during the map which'll be processed during the reduce.
> This would be super simple(in my opinion) for implementation, but it
> doesn't seem like couch offers functionality for generating multi-document
> views(while also hooking into couch's view-invalidation mechanism, so that
> if one of the docs that make up this view is changed, the view will be
> regenerated).  Am I wrong?  Is this something that is planned?
> 
> 
> What does the couch community think of this?  Is there some other pattern
> that might result in a simpler system?
> 
> Thanks for any suggestions you might have, and if I haven't been clear about
> what I want to do, please feel free to ping me on it.
> 
> Mike Kohout

Re: new couch user: "denormalizing" using views? General application architecture suggestions?

[Michael Kohout <mw...@gmail.com>]

I'd rather not do it via batch because I'd like the system to be interactive
(via a browser).  And depending on all this processing to occur outside this
subsystem seems less robust.  And to touch on your point, this is a pretty
big database and exporting like that would make some unhappy dbas.  I'm not
sure these are good reasons, but at least they are the ones I've thought of.

But ultimately what I've taken from this conversation is that the _changes
feed is basically the only way I can build the system I'd like.  Unless
there is some other way that makes sense.

thanks
Mike



On Mon, Feb 7, 2011 at 11:29 AM, Dirkjan Ochtman <di...@ochtman.nl> wrote:

> On Mon, Feb 7, 2011 at 18:14, Michael Kohout <mw...@gmail.com> wrote:
> > What does the couch community think of this?  Is there some other pattern
> > that might result in a simpler system?
>
> Simple system: use SQL JOINs to fetch all the data for each document
> you want in CouchDB, then batch-load those into CouchDB (per few k
> documents) as the rows come in from the RDBMS.
>
> I'm not sure why you'd want to do it some other way? This is the way
> I've done it a few times, though admittedly on smaller databases. I
> don't think your idea for doing joins in a view is particularly
> feasible, though there are things you can do here.
>
> Cheers,
>
> Dirkjan
>

Re: new couch user: "denormalizing" using views? General application architecture suggestions?

[Dirkjan Ochtman <di...@ochtman.nl>]

On Mon, Feb 7, 2011 at 18:14, Michael Kohout <mw...@gmail.com> wrote:
> What does the couch community think of this?  Is there some other pattern
> that might result in a simpler system?

Simple system: use SQL JOINs to fetch all the data for each document
you want in CouchDB, then batch-load those into CouchDB (per few k
documents) as the rows come in from the RDBMS.

I'm not sure why you'd want to do it some other way? This is the way
I've done it a few times, though admittedly on smaller databases. I
don't think your idea for doing joins in a view is particularly
feasible, though there are things you can do here.

Cheers,

Dirkjan