Bill of Materials

[gaccep]

I would like to use mongodb for a Bill of Material planning software
has someone tried to this so far?

Some tips or links would be very helpfull.

Thanks

[gregorinator]

On Tue, May 22, 2012 at 6:04 PM, gaccep <germ...@gmail.com> wrote:
> I would like to use mongodb for a Bill of Material planning software
> has someone tried to this so far?

I have only a year of experience with MongoDB and two systems under my
belt, but I have thirty years of experience with factory management,
Bill of Material, planning, and ERP/MRP software, and I would be
interested to hear why you would even consider MongoDB for this kind
of application. I mean, I'm a MongoDB fan (two systems under my
belt!), but the right solution needs to be applied to each problem,
and Bills of Material are simple data structures, once you accept that
they rely heavily on recursion. It seems to me that for this
application, an RDBMS could be better than a document database,
because the iterative processing required for the recursion might be
more easily implemented in an RDBMS. Or, even if an RDBMS doesn't
make it any easier, at least it wouldn't make it any harder.

I can't imagine you would be considering using MongoDB to define each
document as a complete top-to-bottom Bill of Material, unless you have
a factory in which there is absolutely no reuse of subassemblies
between higher level assemblies. In a typical factory, with reuse of
subassemblies, maintenance of complete top-to-bottom BOMs would be a
nightmare. And if, on the other hand, each document is simply a
single-level BOM and you have to hit the database over and over again
to drill down through the levels, where do you see the benefit of
MongoDB over an RDBMS, which can use the same data structure?

I'm not saying MongoDB would be any worse, necessarily, than an RDBMS,
for a BOM system. I'm just saying that I'm not seeing that it would
be any better, unless you have a different insight for me. Also, it
would be important to consider the read efficiency of your document
structure, since a BOM system is typically overwhelming read (during
BOM explosions, work order creations, kitting operations, MRP/ERP
runs, etc.) as opposed to written.

gs

[gaccep]

I was more concerned with other parts of the system like, Purchase
Orders and Items details like nutrition facts, prices, price history.
Relationships that i could easily model with MongoDB
and you are rigth is a complete nigthmare. I asked the question
because the part of the system that is blocking my migration to
Mongodb is the Bill of Materials.

thy

On 22 mayo, 21:00, gregorinator <gregorina...@gmail.com> wrote:

[Sam Millman]

Why dont you explain the "Bill of Materials"? It sounds very....American-ie so people here might not actually know how to respond to your question.

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

[gaccep]

OK the Bill of Materials is for Food Menus that a company delivers in
a lot of hospitals located in different cities.

A Hospital can have N Menus each Menu have N Preparations which at the
same time uses N Ingredients with different Quantities.
The Goal is to retrieve the items quantities needed for each menu
given a Hospital name, so purchase people could then generate
the purchase orders for the different suppliers. Most of this is based
on a Menu Schedule for example in Week 1 Sept 10 to Sept 17 we are
going to
deliver Menu 1, Menu 2 Menu 3 ,. .etc, in Los Angeles Hospital so
purchase people wants to calculate what are the quantities of items
needed for those given menus in that specific week.

gs

On 23 mayo, 09:36, Sam Millman <sam.mill...@gmail.com> wrote:
> Why dont you explain the "Bill of Materials"? It sounds very....American-ie
> so people here might not actually know how to respond to your question.
>

[gregorinator]

On 5/23/12, gaccep <germ...@gmail.com> wrote:
> I was more concerned with other parts of the system like, Purchase

Okay.

I guess if it were me, my first thought would be to have a collection
in which each document represented one level in a multi-level
explosion, with the next-level components and their quantities in an
array, like so:

{ "_id": "Menu 1", "next level items": [ { "item":"bogey1", "qty" : 2
}, { "item": "bogey2", "qty" : 1 }, { "item": "bogey3", "qty": 3 } ] }

Since this is a multi-level bill of material (BOM) concept, each item
in the "next level items" array can itself be a bill of material, so,
like this:

{ "_id": "bogey1", "next level items": [ { "item": "carrots", "qty": 2
}, { "item": "onions", "qty": 1 }, { "item": "saffron", "qty": 0.25 }
] }

You would have to loop in your client code to iterate through the
levels to "explode" down to the required quantities of the lowest
level items, which is what your Purchasing agents need to buy. Not
pretty, but Bill of Materials never really is. This is no messier,
and might even be a little more elegant, than the same solution in an
RDBMS.

Your other option would be to denormalize the model to the extreme
that you have a complete top-to-bottom Bill of Material -- all levels
-- in a single document. That would make reading data extraordinarily
simple, but then maintenance could be a real challenge. If, for
example, "cumin" gets added as a "next level item" in "bogey1", and
"bogey1" occurs in many menus. As always in a NoSQL data design, how
you are going to use the data, how volatile it is, etc., are questions
that have to be considered as part of the design process. The
structure that I proposed might strike a balance in your situation.
Or not.

Information about what menus are served in which hospitals in which
weeks (i.e., demand) would be in a separate "Schedule" or "Demand"
collection, not your "BOM" collection. The "Schedule" collection
would tell you how many of what "top level" items (menus) you need,
and when. The "BOM" collection would then tell you what dishes and
ingredients are required to manufacture each of those top level items.
Your MRP client program would read those two collections to produce a
third collection of planned or projected orders for your Purchasing
department.

I apologize if I seem to be preaching the obvious. I'm just trying to
respond to the level of detail that was in the question.

HTH,
gs

[gaccep]

Thank you, i really prefer to use suggested option 1, because it´s
easier to model. But
it seems challenging for making updates for quantities. I forgot to
tell that
menus are changing every six month. Looking at your suggested option
it also could
be modeled using a hierarchical structure, o tree structure.

but........

Can an Hybrid option be helpfull ? using MongoDB for scanned invoices,
Items ( Ingredients), User Accounts,
Generated Purchase Orders in PDF, Items descriptions and Hospitals
profiles. And for Bill of Materials, Inventory
and Purchase Orders MySQL Database.

gs



On 23 mayo, 20:03, gregorinator <gregorina...@gmail.com> wrote:

> On 5/23/12, gaccep <germa...@gmail.com> wrote:
>
> > I was more concerned with other parts of the system like, Purchase
>
> Okay.
>
> I guess if it were me, my first thought would be to have a collection
> in which each document represented one level in a multi-level
> explosion, with the next-level components and their quantities in an
> array, like so:
>
> { "_id": "Menu 1", "next level items": [ { "item":"bogey1", "qty" : 2
>
> }, { "item": "bogey2", "qty" : 1 }, { "item": "bogey3", "qty": 3 } ] }
>

> Since this is a multi-levelbillof material (BOM) concept, each item
> in the "next level items" array can itself be abillof material, so,

> like this:
>
> { "_id": "bogey1", "next level items": [ { "item": "carrots", "qty": 2}, { "item": "onions", "qty": 1 }, { "item": "saffron", "qty": 0.25 }
>
> ] }
>
> You would have to loop in your client code to iterate through the
> levels to "explode" down to the required quantities of the lowest
> level items, which is what your Purchasing agents need to buy.  Not

> pretty, butBillof Materials never really is.  This is no messier,

> and might even be a little more elegant, than the same solution in an
> RDBMS.
>
> Your other option would be to denormalize the model to the extreme

> that you have a complete top-to-bottomBillof Material -- all levels

[gaccep]

In Spring Data they use a DBref, as i see it is like
using a kind of foreign key between collections, in your experiences
 do you
think this could help to normalize database and facilitate in some
manner future upgrades to the model?


I could create a Menus with @embedded Preparations and for each
Preparation
use a DBref for an item on the Items Collection. I would prefer to do
this since
Items have many features like price, nutrition facts, etc. It would be
better to
model items in a different Collection.

On 23 mayo, 20:03, gregorinator <gregorina...@gmail.com> wrote:

> On 5/23/12, gaccep <germa...@gmail.com> wrote:
>
> > I was more concerned with other parts of the system like, Purchase
>
> Okay.
>
> I guess if it were me, my first thought would be to have a collection
> in which each document represented one level in a multi-level
> explosion, with the next-level components and their quantities in an
> array, like so:
>
> { "_id": "Menu 1", "next level items": [ { "item":"bogey1", "qty" : 2
>
> }, { "item": "bogey2", "qty" : 1 }, { "item": "bogey3", "qty": 3 } ] }
>

> Since this is a multi-levelbillof material (BOM) concept, each item
> in the "next level items" array can itself be abillof material, so,

> like this:
>
> { "_id": "bogey1", "next level items": [ { "item": "carrots", "qty": 2}, { "item": "onions", "qty": 1 }, { "item": "saffron", "qty": 0.25 }
>
> ] }
>
> You would have to loop in your client code to iterate through the
> levels to "explode" down to the required quantities of the lowest
> level items, which is what your Purchasing agents need to buy.  Not

> pretty, butBillofMaterialsnever really is.  This is no messier,

> and might even be a little more elegant, than the same solution in an
> RDBMS.
>
> Your other option would be to denormalize the model to the extreme

> that you have a complete top-to-bottomBillof Material -- all levels

[gregorinator]

I've never used Spring Data, so I can't help you there. I think DBRef
is one of those features of MongoDB that may look more useful on paper
than in actual practice. I've read that some drivers are capable of
automatically retrieving secondary documents linked by DBRef to
primary documents, but will this iterate recursively so it would be
useful in a multi-level Bill of Material system? I don't know, I'm
just throwing the question out. In my experience, I've always found
it enough to store the id of the referenced document as a simple data
field in the referencing document, as I did in the Bill of Material
example, and retieved the referenced documents myself when it turned
out that I needed them.

Mixing MongoDB with an RDBMS in a hybrid system can be a great
solution! Inventory, in particular, is a great example of an
application where the data is usually highly structured, lending
itself to the RDBMS model, and where it's often crucial to have ACID
reliability, so using an RDBMS for inventory might be a great fit. On
the other hand, I'm not sure why you would consider the RDBMS for
purchasing, since the data structures in a purchasing system, like
Purchase Orders, and inherently document-like and would seem to lend
themselves well to a document database. I think Bills of Material are
somewhere in between -- they could possibly be handled equally well
using and RDBMS or MongoDB.

What are you using for your client-side programming language? I ask
because if you're using PHP, there's an ORM called RedBeanPHP that
makes it incredibly easy to wrap your MySQL database in an object
model, and that would make it even easier to combine an RDBMS data
source and a MongoDB data source in a single application.

It sounds like you're tackling a pretty wide-ranging project. It's
none of my business, but have you looked at buying some of the more
common types of systems, like Purchasing and Inventory, off-the-shelf,
and only customizing the parts that aren't available commercially,
like the menus? It seems like you might be reinventing a lot of
wheels.

HTH, and good luck!

gs

[gaccep]

Thanks you have helped me a lot with this process.