Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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