What is the best way to form a relationship in a table?

Question

PHP

Good evening.
A very interesting and serious problem arose, mostly related to optimization. I have no idea how best to implement this system.
In short, mandatory: the TK spells out the need to create a system that is fully optimized for a load of up to 5,000 RPS and up to 1,000,000 users.
And now the crux of the problem. There are three entities: user, subscription, and discount. A user can have several subscriptions active (standard many-to-many relationship), but at the same time, each subscription has a huge number of discounts (on average, from 400 to 1800).
The main problem: the user activates the subscription, while he spends not subscriptions, but discounts (they can be used a limited number of times). Bottleneck: Need to display the number of remaining discounts across all subscriptions.
My solutions, of which there are only two. First - we create a one-to-many relationship for the user and the subscription, and spend discounts in another relationship: let's say user - discount. In such a case, the data retrieval operation to display the remaining subscriptions would be very costly in terms of resources.
The second option - when creating a subscription, we can roll out all the discounts that are in the subscription to the user-discount table. In this case, the most expensive operation will be the operation of creating a user (writing 1800 values ​​into the database at once for one user is a pain) and soft deleting the subscription (deactivation).
Question: what is the best way to make such a system? I will be grateful for any answer!

Answer 1

discount table minus the discount spending table, well, cache it somewhere so that you don’t request a calculation every time