Which database design is more correct?

Question

SQL

There are 3 tables :

• Products
  
• categories
  
• Discounts
  

Each product (Product) is associated with one category (category_id => id). Everything is clear here.
Each Discount is associated with one or more products, with one or more categories.
Question : how to connect them and where can I read about patterns for building a database structure?

Answer 1

Discounts needs to be linked with two many-to-many, this is done using two auxiliary tables. Thus, you have one Discount can
1) have no connection with either a product or a category
2) be associated with many products
3) be associated with many categories
4) be associated with both several products and several categories

Answer 2

there should be more tables
Products
Categories
Discounts
Discounts_Products ( id | discount_id | product_id )
Discounts_Categories ( id | discount_id | categories_id)
Docks to link through another table:
www.yiiframework.com/doc-2.0/guide-db-active-recor...
(search for "Relations via a Junction Table")
as an option, the last two can be replaced by 1, but experience is more cumbersome:
like d_p_c ( id | discount_id | product_id | category_id)
or even like this ( id | discount_id | related_item_id | related_item_classname)

Answer 3

By the way, it should be borne in mind that in real life often "discount for a discount" is not applicable.
Therefore, it is better to approach the "rules of discounts" in advance.
As one of the options:
1. table of groups (categories) of discounts rules_groups (id, descr, [ordering])
2. table of "rules for applying discounts" rules(id, group_id, descr, ordering, percent [conditions])
It is assumed that from each group, one rule can be applied (first in order).
then, respectively (simplified)
select top 1 percent from rules where group_id=xx and (a bunch of conditions for matching the rule) ORDER BY ordering
moreover, there can be many columns of conditions for rules and it can be easily expanded. Offhand from the general type of categories, product groups, colors, balances / stocks / turnover, ABC classification, etc., ending with expiration dates, quantities and even data of the counterparty, its group and category.
According to taste, at the level of groups or rules, it is possible to provide for a multiplicative sign (multiplication by a coefficient), this is a discount or additive (subtraction of the amount) and "per unit" or "per batch".
Well, taking into account dances with financial circles on the topic of VAT, it’s not bad to have the sign “With VAT” / “Without VAT” (but this implies the nuances of keeping prices.
Another element of tuning will be flags or coefficients on whether to apply the rule when the selling price is reduced below cost "