How to make a Stock with CouchDB?

[Markus Burrer <mb...@embedit.de>]

Hi, I want to make a little material resource planning software with
CouchDB. I have different places, where I can store the material, for
example incoming goods, outgoing goods, different kind of racks and so on.

It should be possible to move the material from one place to another and
I want to calculate the amount of material I have in total.

How can I realize that with CouchDB? Should I store the stock values in
the article documents? Or should I create documents for the stock places
and add/remove the material to these documents?

I have no idea how to do that with CouchDB.

Regards
Markus

Re: How to make a Stock with CouchDB?

[Aurélien Bénel <au...@utt.fr>]

Hi Markus,

> How to make a Stock with CouchDB?

If I were you, I would adopt the "banking recipe":
http://guide.couchdb.org/draft/recipes.html

As explained in the recipe, storing transactions as documents:
- scales well,
- avoids conflicts,
- provides you with a complete log.


Regards,

Aurélien

Re: How to make a Stock with CouchDB?

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

Makus

What you need to be very careful of here is CouchDB lack of ACID transactions across multiple documents.

The best thought of the top of my head would be one document per stock item and hold the usual "parts master type data" in the document and then have an array of stock movement elements. You could then use map / reduce to give you summary stock levels (on hand / on order etc) in multiple locations. Each time you did something with a stock item you would add a new element to the stock movements array. 

That works but there are 3 problems.

Your stock item document could get very large very quickly depending on how many stock movements you are going to have
As you would have to have up-to-date stock counts at all times you would potentially occur a lot of overhead on queries because the map / reduce would have to do an update on every query. This is the default buy it there is a lot of activity it will consume a fair bit of resource.
If your are allocating multiple stock items to a work order then you are going to update multiple documents and as CouchDB itself cannot wrap the update of several documents in a transaction such that you get all the updates done or none of them you will have to program around this.

You can deal with items 1 and 2 by having one document per stock item per accounting period. That keeps document sizes in check but you need a period end run (which you probably would anyway) to create the next periods documents with the opening stock at each location.

Assuming each work order has a unique ID then item 3 isn't to hard to deal with you just record the WO id in each stock movement record and check to see if the transaction for the WO exists in the stock document before adding it to the array.

I think however this is just scratching the surface of the complexities.

Thats the best I can come up with.

Bob


On 24 Feb 2011, at 11:49, Markus Burrer wrote:

> Hi, I want to make a little material resource planning software with
> CouchDB. I have different places, where I can store the material, for
> example incoming goods, outgoing goods, different kind of racks and so on.
> 
> It should be possible to move the material from one place to another and
> I want to calculate the amount of material I have in total.
> 
> How can I realize that with CouchDB? Should I store the stock values in
> the article documents? Or should I create documents for the stock places
> and add/remove the material to these documents?
> 
> I have no idea how to do that with CouchDB.
> 
> Regards
> Markus