little eCommerce question

[imosri]

I am setting up a simple page.

By request it resembles a POS screen. A master ticket record is already created and then go to this items/quantity screen.

The user has only to capture quantity and item SKU and click the insert button, the page reload itself showing the master ticket record, Subtotal, discount, tax and total updated (by a store procedure) and all the items entered.

There is a final "Terminate transaction", that changes the status of the Master ticket record, then goes to another page for payment options.

So far, this all is already done. But internally when the user submits a new item I have to select discount avaible for the item. The discount could come from 5 different tables/attributes:

Product discount
Type of product Discount
Unit discount
Provider discount
Customer discount

All but the last one are related to the Product table with a foreing key on the database. On the page I know the customer Id, so the query for getting the best discount is also done.

The question here, finally, is how to insert that best discount value?. A trigger will be the best option?

Your suggestion will be trully appreciated.

Regards

 

[bhuninghake]

You could use a case statement to select the lowest or best price.

 

[imosri]

Hi,

Thank you for your suggestion.

I was looking for other alternative but I ended with a big CASE trigger

Thank you