Mysql - change database structure - database scenario

Question

I have a given scenario, the user (my client) wants to give discount for the coming thanks giving season. (or) if you purchase more than $X amount he is eligible for free shipping.

1. In order to implement the above scenario, what are the changes to mysql database structure that we need to do ?
2. How to implement it in a query ?

Answer 1

You haven't exactly given us much information to go on (like schema, for instance), but it sounds as though you have a minimum of

• new column(s) to hold discount information, probably in its own table, referenced to your price table via a foreign key
• some sort of logic (perhaps using triggers) that can track the aggregate order amount and set a flag accordingly

To implement these you will need ALTER TABLE etc. commands to effect your DDL. These can be scripted, or if you require flow-of-control logic (i.e. only adding columns if they do not already exist), you will have to pack that into a procedure (in MySQL you can only use -if...else constructs if executing from within a procedure).

Answer 2

Such a scenario doesn't fit within a database neatly. You can certainly store such policies in a database, but actually applying them to the relevant queries is a serious pain. It's best left to client-side logic. Not only do you have to modify every single query that deals with the shopping cart, you also have to make them time/date aware. What happens when the sale ends? A naive system would just set the discount to 0%, but then suddenly all previous sales lose their free shipping discount.