How to calculate the discount?

Question

SQL

Hello. There are 3 tables - Discounts (Discount), Clients (Clients) and Orders (Orders)
Discount

id_discount count_orders percent_discount
         1           5             1,5
         2           10            2,5

Clients

id_client  Name Surname
         1     Ivan  Petrov
         2     Vasya Vasev

Orders The
id order_sum
question is, how to calculate the total amount, given the number of orders that the client has made? If the total number of orders is 5 or more, then there is a 1.5% discount, if 10 or more, then 2.5%. Otherwise, there is no discount.
Thanks in advance

Answer 1

You need to add a secondary key to the Orders table to link to the Clients table. those. add clients_id field. And then, through the inner join, contact the Discount table and calculate the total discount.

Answer 2

in Orders we add id_client
count_orders we do as an interval from 5 to 10 and from 10 to 100000000000000 for example, because otherwise it will be problematic to manage without functions and additional selections

select
Discount.percent_discount
from
Discount
where 
(select count(Orders.id_client) where id_client = ...) BETWEEN Discount.count_orders_min AND Discount.count_orders_max

something like this, I didn’t check for performance, but I would take the logic like this
And I would combine the fields Name Surname , does it make sense to keep them separately?