How to store and query products the price of which depends on a complex set of conditions?

Question

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:

db.packages.insert({
    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:

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

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.

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

PriceComponent

id
fromDate
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]
productFeature* 
product*

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.