Complicated Order Form Help!

[Jeff Campos]

I'm designing a database for ordering wearables (such a t-shirts, jackets, fleece, etc), and am having difficulty in organizing a series of relationships (and maybe calucations) to handle pulling an "appropriate" unit price for each line item. Problem is, each line item unit price is variable, depending on the product itself (a t-shirt versus a polo, for example), it's color (some colors are more expensive than others), and the total number of garments purchased (i.e., a sum of ALL line items).

Moreover, the unit price is affected by how the garment is decorated. Separate pricing exists for screen printing versus embroidery. Within the screen printing variable, a garment might be printed with a 1-color imprint in one location only; or, it may be printed with a 1-color imprint (i.e., in one location) and a full-color imprint (i.e., at another location). Within the embroidery variable, the unit price is affected by the number of stitches required to reproduct the image (from 5,000 to 30,000 stitches, in 1K increments).

Finally, the unit price is affected by the size of the garment. There is generally one price for Youth Small through Adult Xlarge garments. However, 2XLarge, 3Xlarge, and so on, require a premium (i.e, addition to the basic unit price).

Here's where I'm currently at in my thinking . . .

I've created a "Line Item" table, with a PRODUCT and COLOR field (which is placed into my ordering layout as a portal). The PRODUCT field pulls a value list from a "Products" table, and the COLOR field pulls a value list from a "Color Value List" table. (The PRODUCT field serves as the relationship between the "Products" and "Color Value List" tables. Also included is a PRINT LOCATION 1 field, and a PRINT LOCATION 2 field.

So optimally, I'd like to format the UNIT PRICE field to self-generate based on the aforementioned fields (PRODUCT, COLOR, TOTAL ORDER QUANTITY, and PRINT LOCATION 1, and PRINT LOCATION 2). And remember, the unti price is affected by the TOTAL number of units of ALL line items. So in a practical sense, the unit price (for each line item) should decline as subsequent line items are added.

Whew! Does this make sense? Any suggestion on how to proceed?

Thanks in Advance,

Jeff

[sbg2]

And remember, the unti price is affected by the TOTAL number of units of ALL line items. So in a practical sense, the unit price (for each line item) should decline as subsequent line items are added.

I would say in a practical sense the Unit Price should not be affected by the total number of units. I would add a VolumeDiscount field to the orderform that is based on total units ordered.

for example:

Price_ProductTotal = Sum(Order_OrderLineItems::PriceExtended)

Qty_Total = Sum(Order_OrderLineItems::Qty)

VolumeDiscountPercent = Case (Qty_Total < 100; 0; Qty_Total ≥ 100 and Qty_Total < 500; 5; 10)

VolumeDiscount = - (Price_ProductTotal * (VolumeDiscountPercent/100))

PriceFinal = Price_ProductTotal + VolumeDiscount

[Jeff Campos]

I appreciate your response! Your implementation makes sense; problem is, these products are always sold with a declining unit price relative to an increase in volume. As such, the customer wants to see a lower unit price on each line item, versus one volume discount. Unfortunately, that's just how the industry sells these products. One volume discount won't make sense to the customer. Any other suggestions? ;)

[sbg2]

Well the discount percent should still be defined at the order level based on the sum of the Qty. You will have to add a DiscountAmount to each Line Item where DiscountAmount = - (ProductPrice * (Order::DiscountPercent/100)). Then ProductPrice_Final = ProductPrice + DiscountAmount

You may also not want to hard code the DiscountPercent formula (If you are even working with percents?). You may want to store these in a seperate table and use a look-up for each order, this way you can change the discount rules at a later date with out affecting older orders.