You should have a table for each class of thing you want to offer a discount on. From your question that would be a table each for products, customers and stores. Most likely you will have these tables in your design already. Each will have a column of an appropriate type to hold the discount. You don't say what you will offer but a percentage discount could be a
TINYINT UNSIGNED since it can only be in the range 0 to 100 or a
DECIMAL if you prefer to handle your math that way, and so forth if you'll offer fixed-amount discounts ("$10 off your purchase over $120. This month only!"). Name your column to match your rule e.g.
DiscountAmount. If it is important to track changes to discounts over time separate tables keyed by date will be required e.g.
CustomerDiscount (CustomerID, StartDate, EndDate, DiscountPercent).
Be sure to work out with your business how these rules interact. If I'm a 5% customer buying a 6% product do I get an 11% discount or a (0.95 * 0.94) price? For a $100 item will I be charged $89.00 or $89.30? Can I even get more than one discount on a single purchase? Which?
If only a few combinations receive the discount you could have an intersection table keyed by, say, product and customer as you mention in your question. This works up to a couple of levels then gets very unwieldy. Then a general decision table will be your best bet. This will have a column for each of your decision variables (product, customer and store in your examples) and another for precedence/ sort order. You manually assign the precedence to match your rules and at run time pick the highest precedence which matches this sale's parameters.
Store Product Customer Precedence Discount%
Sao Palo 1 2
Melbourne 1 2
Foo 2 3
Bar 2 3
Chris 3 5
Sam 3 5
Foo Sam 4 7
Sam buys some Foo in Melbourne. She gets 7% off since that's the row with the highest Precedence that matches all the criteria (or has a NULL). Anything else Sam buys has a 5% discount. Anyone else who buys Foo gets 3% off.
It is usual to copy relevant values to the
Sales table for each transaction. This gives you
a) a complete audit history, which accountants like
b) flexibility to award non-standard discounts.