# Orthogonal design for product pricing and production planning with a NoSQL database

I am in the process of developing extensible software for the company I am working with for product pricing and production planning.

The Present Scenario

There are basically two types of products: manufactured and traded (simple buy and sell with no conversion). Manufactured can be regular or made-to-order with made-to-order mostly being a deviant of the regular model. There is only a finite number of products (around 2000) but each product has got multiple attributes such as size, color, shade and more. Presently, there is software for order management with inventory and a decent accounting package. Costing and pricing is now done on an arbitary basis via sampling a few models and adding overhead and margin percentages.

Required

• To calculate product cost per unit
• Convert them into a valid pricing model (pricing may differ upon quantity and other factors),
• Calculate exact cost and profitability per product,
• Proactively plan production for regular models and made-to-order units, based on existing order management system and inventory

Proposed Design

I have proposed the following design.

• Store product information for each unique product in document-style based on the 5M's of production (Man, Material, Machines, Methods and Money)
• A separate module of each of the four M's - Men, Machine, Material and Methods in terms of Money with primitive building blocks for each module that can be merged and combined into a compound block
• Store metadata and result set required along with each document
• A separate layer to parse metadata, act upon data based on metadata and return result set as defined (similar to a DSL with both metadata and result set redefined at runtime)
• A separate pricing model that works on the cost of the product
• A separate production planning module that works on the data of the order management system and the product information
• Using MapReduce to aggregate data and analyze it
• Technologies: MongoDB, Python, Django, and NumPy

All data to be stored in JSON format with all business logic written to parse a JSON file (technically, in my case a Python dictionary) and produce output based on keys and values.

So why am I here?

The present software company has recommended a traditional out-of-the-box RDBMS system that could be customized. The main problem I find with the system is the duplication of data for products with varying attributes, lack of analytical tools, tight coupling with products and prices and too many joins for complex queries, but it is recommended for better robustness and better aggregate analysis and the XXXX always told market runs this approach. Since the entire software is not to be used in real-time and most of the real-time sytem links to unique products only.

• Does my proposed design is truly orthogonal?
• Does loose-coupling product, cost and pricing work?
• What is the general approach for this kind of problem?
• Do MapReduce provide a better way to analyze data for this kind of problems (along with NumPy) than the traditional SQL approach

Basically I work with numbers, and I am thinking of a generic model which I could extend in the future.

-
I have nothing against NoSQL db's, I think they are pretty neat. But I think the president of the company is right here. What you describe is a typical line of business system, and I find it hard to believe an RDBMs wouldnt suit the project. –  GrandmasterB Dec 17 '11 at 9:10
Thanks for the suggestion (and thanks to Satyajit too). You are spot on with your RDBMS comment. What I need is a freely extensible model that could be developed as a stand-alone product. Also need to know the general pricing/cost software in market (I just can't see lot of full-fledged software for pricing/cost estimation. Most of them comes bundled with ERP or a spreadsheet is used. Kindly correct my perception if it's wrong). Not starting any flame wars, if Product P has attributes [A(1-4),B(1-6),C(1-5)], you have 120 possible combinations. Star schema may be the solution but I prefer JSON. –  Ubermensch Dec 17 '11 at 12:50