CODEWITHSUNDEEP
mysqllaraveleloquent
user12855055
user12855055

Reputation:

How to deal with discount table?

I have these tables:

categories:

name - created_at

products:

id - name - price - quantity - category_id - brand_id

I have these two tables I need to create discount on my products as owner of the store. So I created table discounts:

discounts: 

id - type('percentage','numeric') - value - cancel - expired_at

So I need:

1- to put discount on all products that belongs to for example category (T-shirt)

2- to put discount on specific product or products that I need

MY shut:

created two pivots tables:

discount_product ( discount_id -  product_id )

discount_category ( discount_id - category_id )

Is that the best practice to do something like that ?

Upvotes: 2

Views: 938

Answers (1)

Arun A S
Arun A S

Reputation: 7006

Your approach is good for your current requirement, keeping two pivots keeps the discount seperated, indexes will give fetching data speed, but the drawback is that you'll need to query from 2 tables to fetch all the discounts ( would be slightly slower ).

Below is just a possibility, you may ignore it, but give reading it a try

But I'd like to mention a few possibilities

  • what if the shop owner wants to apply discounts to a specific brand? ( supposing that you agree to add this feature ) for example he wants all Raymond products to have a discount? With current schema, you'd need to find all products with Raymond's brand_id and add individual entries in discount_product table to all those products ( not really that difficult, but the data in the table would get very large ) or create a new pivot table for brands.
  • Similarly, if the shopkeeper wants discount for all Raymond ( brand ) Shirts ( category I presume ), again no choice but to apply discount to individual products.
  • And what if a discount for only certain gender or age group?
  • Or clothes size

( okay i know I'm overthinking all this as chances are low for such requirements. If so much requirements are present it might just be better to use a library or switch to wordpress/Magento ).

So my point is, although attaching these discounts to individual products seems like the easy way out ( assuming you don't create pivot tables for each type of discount ), if you need more flexibility ( which i personally believe is important as well. A flexible project is easier to expand, and the people who will have to work on the project in the future will curse you a lot less ) would it not be better to keeps single pivot table and differentiate them by a type? ( aaargghhh sounds like a pain...... but is it? )

Well Laravel has a solution for you, Polymorphic Relationship. Yes, now you just need a single table for pivots and let Laravel do the magic ( well, it just stores the models name with namespace in the type column ). It gets a lot more complex if you don't want to use Laravels Polymorph, you'd need a table for type, store each type in this table, and then place a foreign key in the pivot table.

But before jumping in and choosing to follow this method, think if you would actually need it? ( I admit that most of this isn't even in your question, i just mentioned it due to me overthinking it ) Are you certain that you will need just theese two types of discounts? Could you just reject any requests for other types? You don't care about whoever has to clean up your mess? If your aswer is yes, then just stick to your current schema.

So, choose whichever you feel is more appropriate

Upvotes: 2

Related Questions