March 30, 2012 at 6:03 am
just a question to get my thought processes rolling in teh right direction;
another post had a database that had an item that had a percentage as a markup for profit.
i would prefer to track the cost price and the retail price, and calcuate the percentage of markup myself.
That got me thinking, from a database perspective, how would you handle the way sales occur in a grocery store;
besides cost vs retail price (which can vary week to week),
How would you handle buy one get one? if you buy one, it's regular price, and the second one is free?
the very similar $2 dollars each or 3 for $5?
Lowell
March 30, 2012 at 6:32 am
Just an idea:
The standard process would be price lookup, then simple calculation.
Then I would have deal table, which would keep deals per product and quantity, please note: buy-one-get-one-free is not different to 3 for £5 - it's just a name.
Let say something like:
DealId,
ProductId,
DealName,
QuantityApplicable,
DealPrice
When you do lookup of the above per ProductId, to calculate total price, you would do something like (in pseudo-code):
SellPrice = TruncateToInt(SoldQuantity/QuantityApplicable) * DealPrice +
(SoldQuantity - TruncateToInt(SoldQuantity/QuantityApplicable) ) * StandardPrice
You would want to calculate and store both standard and discounted prices for future statistics analyse...
March 30, 2012 at 8:29 am
I just did an online coupon project not long ago and what Eugene described is very similar to what I did.
The main difference for me was I created a coupon table. This gave me some large scale settings of the discount (type of discount, start and end dates, single or multi use, limit to certain skus or not).
Then I had a CouponSku table to track which skus were eligible for any given coupon.
So far this sounds pretty much the same as Eugene's.
The last piece I added was a CouponDiscount table. This allowed me to define multiple discount levels using a single coupon. This way I could accommodate things like $10 off $100 order, $25 off $200 order. This design was flexible enough to handle both Percentage discounts and Purchase discounts. Depending on the type of discount in the Coupon table I can either multiply the quantity * the appropriate discount level (for percentage discounts) or subtract the appropriate discount level from the total (of course remember that tax is paid before the discount).
The process I wrote allowed for only a single coupon per purchase. In the grocery store type you would obviously need to allow for any number of coupons per purchase. The only real difference is in my case I stored the CouponCode at the order level, for your grocery example you would have to store it at the OrderDetail level.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 30, 2012 at 8:34 am
both answers help me visualize this a lot, thanks guys;
The Coupon/Discount table's the way I'm thinking as well...it's like Sean said, the discounts apply to the order, not the individual items, which is where i was losing myself.
those discounts apply if the order meets various criteria; much easier for me to visualize now.
Lowell
March 30, 2012 at 8:39 am
To allow for the multiple discounts/coupons you might need to have an OrderDetailCouponDiscount and OrderDetailCouponSKU tables or something along those lines so you know which coupon(s) were applied to the order and which skus were eligible for each coupon.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 30, 2012 at 9:07 am
You may want to double check what you need to keep track of. In the scenarios where I've had to implement this type of tracking, you need VERY specific rules which scenarios represent "temporary prices" (price is essentially a SCD in this scenario and fluctuates over time) vs discounts (where the price itself doesn't change, but you choose to give some money back to the customer) vs manufacturer coupons (where the price itself doesn't change, but you collect some of the money from the customer and some from the manufacturer).
The accounting requirements make the tracking of the various scenarios reasonably different.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 30, 2012 at 9:12 am
Good points Matt!!! Tracking for me is pretty simple because it is all in house promotions. As a result we don't need to worry about collecting money from manufacturers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply