/    Sign up×
Community /Pin to ProfileBookmark

Design / Structure

Copy linkTweet thisAlerts:
Nov 06.2020

I have a database table with a range of products each have a price dollar value.

I need to load a ‘sale’ into my sales table, each row has a name, start and end date. Then if between the dates the discount is applied where appropriate.

I was doing to add a column percentValue and dollarValue where it can be one of the other, or have a discountType 1=percent, 2=dollar then discountValue being percent or dollar. So if 2 and 20 it’d take $20 off the price when displayed on the screen.

Reading through my requirements. I need to apply to all or selected products.

I want to have to have a table SaleProducts JOINING ProductID and SaleID together. Therefore seeing if a sale is linked to a product which may be all.

Finally. My issue.

How can I do it so the discount can be applied to each product individually?

I thought of adding columns discountType and discountValue into SaleProducts?

But what if I load a new years sale now and I later add new products do I have to then go link them?

Looking for ideas.

to post a comment
PHP

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@sibertNov 06.2020 — > @kiwis80#1624872 How can I do it so the discount can be applied to each product individually?

What have you tried? Preferable with a dbfiddle example.
Copy linkTweet thisAlerts:
@NogDogNov 06.2020 — If you are going to have to support multiple types of product/sale groupings, I feel like you may have to go with the product-to-sale relationship table. To support those different types of sales you may then need to create some admin tools to implement them:

  • - One to just add all current products to a sale (part of the tool that creates a new sale?)

  • - One that allows an admin to pick and choose which products are assigned to a sale (also part of the sale creation tool?)

  • - Maybe one that does something like add all products of Brand X to a sale (ditto again)


    • Additionally you might want to add something to the admin tool that allows you to add new products to assign them to a sale at that point.
    Copy linkTweet thisAlerts:
    @kiwisauthorNov 06.2020 — @NogDog#1624889

    Yeah, that's where my mind is moving to as well. So, the sales table will have the basic essentials, SaleID, SaleName, ,SaleDescription, StartDate, EndDate and some other potential options such as 'terms and conditions'

    This will be via a simple web form with rules and logic built in via HTML and PHP (EndDate not before StartDate etc)

    A second table be SaleProductionRelationship (better name to be confirmed)

    SaleID, ProductID, DiscountType, DiscountValue

    A second form and process will be applied in a three step approach. The admin will select a specific sale then select all the products that apply to the select sale with a 'select all' link. That's step 1.

    Step 2 will allow them to toggle between Group or Individual Policy. Group is when the same discount applies to all products selected (10% off Selected products)

    Step 3 will be applying the discount type and value. If group is selected a single form will be displayed with type (Percent or Dollar) then a value. If Individual is selected each item will be displayed with the same two fields as above but for each product.

    Then once saved the SaleProductionRelationship table will have the DiscountType and DicountValue for each product in each row.

    My 'add new product' process will need to include a final step which will display all future or current sales that I may want to include this product into.

    It could be as simple a adding a checkbox? Especially if the Group Policy was selected above, perhaps I could capture that in the Sales Table?

    Thoughts?
    ×

    Success!

    Help @kiwis spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 4.24,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,

    tipper: @Samric24,
    tipped: article
    amount: 1000 SATS,
    )...