How to write a simple affiliate program? I'm more concerned with the structure of the database.

Question

PHP

Guys, hello! I apologize in advance for such a noob question and a lot of LETTERS, I'm new to site building, now I'm sitting and racking my brains on how to make a database architecture. This is my first design experience and I would like to design a database normally.
The affiliate program has the following requirements:
1. You do NOT need a separate affiliate account, it is built into the service itself, i. when you click on the "Affiliate program" menu link, a page opens in front of you, where a link with an individual ID is inserted in the text field - of the form - domen.ru/partnetship.php?p=776677.Further on the same page there is a table with a sales plan, i.e. it checks how many payments were made in 30 days by invited people using the link with ID 776677, if 3 payments for the service were made, then the partner is charged one percent, for example, 10% of the total amount. If there are 10 payments, for example, there is already 20%, and so on.
2. It is necessary to count the number of clicks on the partner's link for 30 days, i.e. number of clicks per page.
3. It is necessary to count the number of registrations on the partner's link for 30 days.
4. The total number of payments registered through the affiliate link for 30 days.
5. The total amount of attracted payments for 30 days on the partner's link.
6.Well, and accordingly the amount of earnings by the partner for 30 days, according to the total number of sales based on which, the partner is paid a certain percentage.
I only form tables, so you can offer different options.
What actually happened to me is an approximate version:
CREATE TABLE `users` (
​​`user_id` int(11) NOT NULL AUTO_INCREMENT ,
`name_user` varchar(50) NOT NULL ,
`lastname_user` varchar(120) NOT NULL ,
`email_user ` varchar(125) NOT NULL ,
`pass_user` varchar(60) NOT NULL ,
`phonenumber_user` char(18) NOT NULL ,
`register_date_user` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`confirm_status ` tinyint(1) NOT NULL ,
`confirm_hash` varchar(32) NOT NULL ,
`user_ip` varchar(16) NOT NULL ,
`id_affiliate` int(5) NOT NULL ,
`id_affiliator` int(5) NOT NULL ,
PRIMARY KEY (`user_id`),
UNIQUE KEY (`email_user`),
UNIQUE KEY (`id_affiliate`)
)ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
Where confirm_hash is written - this is necessary to confirm user registration by mail.
As for register_date_user - I use datetime because the timestamp is limited to 2037 - well, just in case, the data will be updated through the NOW () function.
As for id_affiliate - this id is assigned during user registration, it must be unique so that there are no repetitions.
id_affiliator - here I think to write down the id of the one by whose link the user registered. - but here the question arises with the UNIQUE KEY (`id_affiliate`) parameter - no matter how it leads to a failure in the table, although on the other hand this is a completely different column and, in fact, should not cause any conflict.
I understand that it will be necessary to parse the link that the visitor clicked on and pull out the id from there, for example 776677, and put it in the session or in the cookie. Then, when registering, you need to write this value into id_affiliator in order to understand which user the new user is assigned to, and use this to account for commissions.
Here further I have a stupor, how to make architecture. I do not quite understand how the structure should be. I assume that in the second table, for example partner_programm, there should be the following:
1.Primary key
2.id_affiliate - foreign key to the users table to the id_affiliate column.
3.clicks_on_partner_link - the number of clicks on the link in 30 days.
4.register_for_link - the number of registrations through an affiliate link in 30 days.
5.count_payments_for_link - the number of payments by invitees in 30 days.
6.summ_payments_for_partner_link - the total amount of payments by invitees, for 30 days. - this amount is made up of the table of payments - billings - according to user_id for the last 30 days.
7.earn_affiliate - affiliate earnings based on the number of payments, invited, for 30 days and according to the sales plan, which is described at the beginning.
8.earn_paid - funds paid or not, boolean type, i.e. if paid, then 1, if not, then 0.
Billings table contains:
1.billing_id - primary key.
2.billing_for_user_id - foreign key to the users table to the user_id column to understand which user has paid for the service for 30 days.
3.tariff_user - user's tariff
4.summ_payment_user - payment amount
5.date_payment_account - account payment date
6.date_end_payment_account - account activity end date.
7.number_payment - order number - here I think you can use billing_id instead of this column? just increase the AUTO_INCREMENT range so that not a unit is used, but for example 12.
8.status_payment - payment status - active (if the payment went through), pending (if the person went to the payment system page and suddenly closed the tab).
Guys, please tell me if the train of thought is correct, and is everything okay? If not, please tell me what and how. I will be extremely grateful.
There is another question, how to clean the data every 30 days in the database?
Let's say that the period has passed in 30 days, the partner's counters should be reset, i.e. the number of clicks on the link, registrations of invitees, the number of payments, the total amount of funds raised - but the amount earned should not be reset if he did not order the payment of funds.
Payment of funds is planned to be made on certain days, for example, from the 1st-5th day of each month. The partner must order the payment himself these days, if he sends a request for payment, then we set earn_paid 1 when sending the request, i.e. the order of funds has been made and the data in earn_affiliate is reset.

Answer 1

Thank you all, I created the structure myself. I will now test it and bring to mind! Great holidays to all!