Apologies if this is too specific a question or in the wrong forum!

I am developing an ecommerce system for selling clothing, the client records each individual size of any particular item as a unique stock unit so for example we might have a blue shirt available in 4 different sizes (and prices) and these will be 4 individual stock items.

So my database has a table Products and a table ProductSizes so the parent record in products would be 'Blue Shirt' and the 4 child records in ProductSizes would each have a different size (and sometimes price) and unique stock code.

This works fine as we can browse by Product and then view the product and see and order the individual stock items.

Now the client wants to have special offers where for a fixed price you can buy 10 of the same product in different sizes.

Does anyone have any recommendations or tips on how to handle things like special offer packages which override individual unit costs in situations like this?

How does the accounting work? The database needs to reflect the real world, so you need to understand how their accounting system records this kind of transaction before you can correctly determine how to store it.

-PatP

Thanks Pat, my database mirrors their accounting system to each size of garment is a unique stock item. The only difference is that the accounting system uses a flat table of stock items but I have the database group the stock items together so all 'Blue Shirt' sizes are grouped under 'Blue Shirt' so when browsing the catalogue it doesn't show 4 blue shirts, just one with 4 sizes.

They're fairly flexible about how they do it in the accounts system, they have to add each item to an invoice for stock control to work but they can either reduce the item costs so the total equals the special offer total or they can zero all the items and add a special non-stock item at the end of the invoice for the total amount.

I basically need the shopping cart at the end to contain all the individual items but have the special offer total price rather than the regular total price.

I know it's a bit vague and I'm not looking for a definitive answer but I'm just thinking of ways of doing it and curious how other people's systems cope with storing and applying special offer packages.

Steve.

It sounds like a discount rule, each rule triggers a different type of discount in code, the discount is probably a class module, with new discounts being applied in the class moduel. soemtimes you can do the discount entirely in SQL depending oin complex the discount rule is, and how transparent your data is.

it depends on how tightly the rule is drawn up
you would need some mechanism to identify what are qualifying products, and what products qualify for one category but not another category.

so it maybe that the manufacturer has to be the same, the manufacturer product has to be the same. most manufacturers treat a shirt of design x the same but qualify their stock number with some trailing digits to indicate size or colourway

it may also depend on how many special offers the customer has running concurrently, and how often they change them

I'd suspect the customer needs to qualify their product range with some coding so that you can base a discount rule. That coding may well involve using the manufacturer stock number.

you need cleear guidance from the customer as to how they structure their offers
its could be
buy x products from manufacturer y get n% discount
buy x products from the same product range from this manufacturer
buy x shorts, irrespective of manufacturer or size
or much tighter buy x shirts of the same design but different colours, or sizes
until you have got that bottomed out Im not sure you can progress further

the discount rule could be the detail SQL, or a reference to the stored query identifying the the SQL to apply the discount.. mebbe move the business logic applying the discount to a specific stored procedure.. specific to each discount offer

Based on the information you've posted so far, I'd approach this as an inventory item for the discount, kind of like UPC discount coupons. When the order is rung up, the individual items initially ring up at their normal price and the discount rings up at zero. Once the discount criteria are met, then either the discount itself or the discounted items get price adjusted. Nearly all POS terminals handle this kind of processing automagically for you.

-PatP

Thanks guys, I've been experimenting with various different ways of doing it - some which work better than others but the discount rules idea seems the most flexible way for different special offers.

I'll play around and work out how to store my rules and how to check if an order matches those rules now