销售点的库存数据库（任何x为x英镑）

Question

I'm working on an inventory / invoice system written in MySQL / PHP (possibly with some kind of Access front end as well as web interface).

The problem is that I want the inventory of products to be able to cope with items that have discounts for multiples such as "any two for £10". What I want to happen is if say 10 items are added to an invoice, it can work out if any of those items meet the "any x for £x" requirements and apply the discount. The added complication is that any item could have different multiple discounts such as "any 2 for £10" as well as "any 3 for £12". Basically like what the supermarkets do - nice an easy for the end user.

It works fine if I want any x of the SAME item entered on to the same invoice line but it needs to be more flexible than that.

Does anyone have any ideas on how I could do this? Thanks in advance. I am pretty good with HTML / MySQL / PHP / JavaScript etc.

Answer 1

Working with PHP and MySQL, you could have a "discounts" table where you save the ID for the product, the number of the products, price and name of the offer. In this way, with a simple query, when you are calculating the final value for the invoice you check against the table if an offer for each product exists:

SELECT * FROM discounts WHERE product_id = x AND quantity >= y

So, in the end, you have a table for products and prices and a related table for offers.