How to design a subscription purchase?

Question

MySQL

Hello!) The problem is designing a functional like an online store, but as a product, a "subscription", which allows the site user to have access to certain parts of the site (For example, video tutorials, etc.). The subscription price is formed as for 1 pc. 1 piece - corresponds to 1 day. A referral program is also possible. The client came to the site on a recommendation.
I'm interested in how to optimally design such a system, for example:
1) Where to store and aggregate the total number of subscriptions and how to track how many days until the expiration of, for example, a PRO account is left?
2) What is the best way to organize a referral system?
Now there are such tables
ORDER (id_order, id_abonament, id_client , amount(number of subscriptions - days), status, date_order)
ABONAMENT (id_abonament, price, options)
CLIENT (id_client, name, email)
LOYALTY_JOURNAL (id, id_client
, login_ref (recommended user's login)) design only). Let's discuss!

Answer 1

The total number of subscriptions.
We have a "User" table and a "Subscription" table (here is the term and price). We create the third table "UserSubscription", which will contain the user id, subscription id and subscription purchase date.
We parse "date of purchase of subscription" and "period of subscription" from the database, add one to the other and compare with the current date. If the current one is greater, we remove the PRO status for the user. Of course, we put this script in cron and run it every 24 hours
How best to implement a referral system 3) When registering, checking if there is a cookie set means counting this user as a referral to another.
1) Since those. side - you form the link example.com/index.php?refid=1 Refid - there is a user id in your database.
2) If the user clicked on such a link - set a cookie for 30 days,
With RefID - the simplest example. You can combine id + date of registration in unixt time and then take sha1 from this thing. This option is considered more secure, but I still don’t think it’s right to show user id.
Also I recommend to esteem to you about normal forms of a DB. If you understood them, you would not have had the first question. In practice, only the first three are widely used. From the fourth and beyond - already all sorts of specific solutions.