Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Product, Order and discounts

    Hello.

    I have a simple shop setup with a few tables; Customer, Category, Product, Order and OrderItems. Nothing special here.

    Now, during some seasons/events (X-mas, Easter etc) it's nice to offer customers a discount on all or certain items in the product line.

    Offering discounts for returning customers is also appreciated, bargain discounts (buy 2, get +1 for 40%), volume discounts (20% on 100+ items) or simple discounts like "get 20% on your first order".


    First I thought I'll add a Discount table and simply connect the concerned products to it, but in the later scenarios it's certainly not enough.

    I'm starting to think I'm way over my head here, unless someone could give me a nudge in the right direction.

    Thank you.

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Discounts can be tricky. Here is my solution to the question but it isn't all inclusive. Usually when you have buy 2 get 1 free it includes the phrase "of equal or lesser value". I haven't dealt with that. There are also lots of other issues you might run into, but at least here is one answer:

    Discount
    DiscountID
    DiscountName
    StartDate
    EndDate
    CustomerSpec - All, Returning, New
    ProductSpec - All, Category specific, Product specific

    DiscountVolume
    DiscountID
    StartCount
    EndCount
    DiscountPercent

    DiscountCategory
    DiscountID
    CategoryID

    DiscountProduct
    DiscountID
    ProductID

    EXAMPLES:

    Offering discounts for returning customers:
    Discount: 1, 'Returning Customer 5%', 01/01/1900, 12/31/2099, 'R', 'A'
    DiscountVolume: 1, 1, 999999, 5%

    Bargain discounts (buy 2, get +1 for 40%) for specific category:
    Discount: 2, 'Office Supplies - Buy 2, get 1 40% off', 10/1/2009, 10/31/2009, 'A', 'C'
    DiscountCategory: 2, 77
    DiscountVolume:
    2, 1, 2, 0%
    2, 3, 3, 40%
    2, 4, 999, 0%

    Volume discounts (20% on 100+ items) for specific product:
    Discount: 3, 'Soccer Balls - 20% off after 100', 11/1/2009, 11/30/2009, 'A', 'P'
    DiscountProduct: 3, 44
    DiscountVolume:
    3, 1, 100, 0%
    3, 101, 999999, 40%

    Simple discounts like "get 20% on your first order":
    Discount: 4, '20% off your first order', 12/1/2009, 12/31/2009, 'N', 'A'
    DiscountVolume: 4, 1, 999999, 20%

  3. #3
    Join Date
    Oct 2009
    Posts
    2
    Thank you very much.
    While you say it's not all inclusive I think it is quite a nice solution, myself I would never come near to anything like it.

    Oh, and I apologize for not answering earlier.

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    It is good to hear my answer was helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •