I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB.

Let the products be packages of cable tv providers:

    name: "Virgin TV",
    price: 20

    name: "Sky",
    price: 25

Every package comes with some conditions. The Sky package for example may be:

  • The first three months the price is XXX then it becomes YYY.
  • If you watch TV after 00:00 is XXX-10.
  • If you are the N'th customer you have 10% discount.
  • ...

But the conditions for the Virgin package will be different.

1) How do I actually store the products along with all their conditions?

I could only think of creating something like stored procedure for every different condition?

It would be nice if I don't have to normalize all conditions to fit a database schema because new conditions can easily come and go.

EDIT : I've realized 2 essentials

  • MongoDB supports anonymous functions as a value of a field.
  • Map/Reduce can transform a collection to another using a function

Now our the Sky package along with its condition can be easily stored:

    name:      "Sky"
    price:     25,
    condition: function(object, user_input){
        time_discount = user_input.time > 0 and user_input.time < 6 ? 10 : 0;
        price = price - time_discount;
        return 6*price + 6*(price*1.2); 

Then if we use mapReduce we can query in like:

            emit(this.name, {...some fields..., annual_price: this.cond(this, user_input)});
        function(key, values) {
            return values;
        {out: "tempCollection"}

I'm not happy with the fact the mapReduce output is a an object which stores the transformed values in a single key named "value". Is there another way to transform a collection other than MapReduce?

------- Outdated ------

MongoDB has server-side functions which definition I presume can say something like:

db.system.js.save( { _id : "sky_annual_price" , value : function(price, user_input){
    time_discount = user_input.time > 0 and user_input.time < 6 ? 10 : 0;
    price = price - time_discount;
    return 6*price + 6*(price*1.2); 

for the Sky example above.

Yes, in theory looks nice but how can this be used in a query?

------- End of outdated ------

2) How do I query such information so that I can sort by the annual price ?

Any ideas on both 1) or 2) are welcome. I've spent considerable amount of time but I can't figure that out.

1 Answer 1


Check out chapter 3 of

The Data Model Resource Book Volume 1 by Silverston

It has a pricing model that is very flexible

A very brief summary:

It's kinda complicated...

(*) means this column references another table


thruDate null
price null
percent null
geographicBoundary* [pizza costs more outside of city]
partyType* [surcharge to seniors]
productCategory* [discount for CRT monitors]
quantityBreak* [discount for high volume]
orderValue* [discount for big order]
saleType* [surcharge for retail]
currency* [discount for Canadians]

BasePrice : PriceComponent

DiscountComponent : PriceComponent

SurchargeComponent : PriceComponnent

ManufacturersSuggestedRetailPrice : PriceComponent

Charge : PriceComponent

OneTimeCharge : Charge

RecurringCharge : Charge

UtilizationCharge : Charge

So, the price of something is a set of PriceComponents, which can be either an actual price amount or a percentage. You can have a component of the final price based on geographical location, the type of sale (retail vs wholesale), the value of the order and so on.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.