Ребят, привет! Прошу заранее прощение за такой нубский вопрос и много БУКВ, я новичок в сайтостроении, сейчас сижу ломаю голову как сделать архитектуру БД. Это мой первый опыт проектирования и хотелось бы нормально спроектировать БД.
К партнерке предъявляются следующие требования:
1.НЕ нужен отдельный кабинет партнера, он встроен в сам сервис, т.е. когда кликаеш по ссылке меню "Партнерская программа", перед тобой открывается страница, где в текстовом поле вставлена ссылка с индивидуальным ID - вида -
domen.ru/partnetship.php?p=776677. Далее на этой же странице идет таблица с планом продаж, т.е. проверяется сколько было совершено оплат за 30 дней приглашенными людьми по ссылке с ID 776677, если было совершено 3 оплаты сервиса, то партеру начисляется один процент, например 10% с общей суммы. Если 10 оплат, там например уже 20%, ну и так далее.
2.Необходимо подсчитывать количество кликов по ссылке партнера за 30 дней, т.е. количество переходов на страницу.
3.Необходимо подсчитывать количество регистраций по ссылке партнера за 30 дней.
4.Общее количество платежей зарегистрировавшихся по ссылке партнера за 30 дней.
5.Общая сумма привлеченных платежей за 30 дней по ссылке партнера.
6.Ну и соответственно сумма заработка партнером за 30 дней, согласно общему количество продаж исходя из которого, партнеру выплачивается определенный процент.
Таблицы только формирую, поэтому можно предлагать разные варианты.
Что по сути получилось у меня - это ориентировочный вариант:
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;
Где написано confirm_hash - это нужно для подтверждение регистрации юзера по почте.
Что касается register_date_user - использую datetime потому что timestamp ограничено 2037-м годом - ну это на всякий случай, обновление данных будет происходить через функцию NOW().
Что касается id_affiliate - этот id присваивается при регистрации юзера, он должен быть уникальным, чтобы не было повторений.
id_affiliator - сюда думаю записывать id того, по чьей ссылке зарегистрировался юзер. - но вот тут встает вопрос с параметром UNIQUE KEY (`id_affiliate`) - в таблице как бы это не привело к сбою, хотя с другой стороны это уже совсем другой столбец и по сути ни какого конфликта вызывать не должно.
Я понимаю что нужно будет парсить ссылку по которой перешел посетитель и вытаскивать оттуда id, например 776677, и засовывать это в сессию, либо в куку. Затем нужно при регистрации это значение записать в id_affiliator, чтобы понимать за каким юзером закреплен новый юзер, и использовать это уже для учета комиссионных.
Вот дальше у меня уже ступор, как сделать архитектуру. Не совсем понимаю как должна быть структура. Я предполагаю что во второй таблице, например partner_programm, должно быть следующее:
1.Первичный ключ
2.id_affiliate - внешний ключ на таблицу users к столбцу id_affiliate.
3.clicks_on_partner_link - количество переходов по ссылке за 30 дней.
4.register_for_link - количество регистраций по партнерской ссылке за 30 дней.
5.count_payments_for_link - количество оплат приглашенными за 30 дней.
6.summ_payments_for_partner_link - общая сумма платежей приглашенными, за 30 дней. - данная сумма складывается из таблицы оплат - billings - согласно user_id за последние 30 дней.
7.earn_affiliate - заработок партнера исходя из количества оплат, приглашенными, за 30 дней и согласно плана продаж, который описан вначале.
8.earn_paid - выплачены средства или нет, булев тип, т.е. если выплачено, то 1, если нет то 0.
Таблица Billings содержит в себе:
1.billing_id - первичный ключ.
2.billing_for_user_id - внешний ключ на таблицу users к столбцу user_id, чтобы понимать какой пользователь оплатил себе сервис на 30 дней.
3.tariff_user - тариф пользователя
4.summ_payment_user - сумма платежа
5.date_payment_account - дата платежа за аккаунт
6.date_end_payment_account - дата, когда закончится активность аккаунта.
7.number_payment - № заказа - вот здесь я думаю может вместо этого столбца использовать billing_id? просто диапазон AUTO_INCREMENT увеличить, чтобы не единица использовалась, а например 12.
8.status_payment - статус платежа - активен(если оплата прошла), ожидает(если человек перешел на страницу платежной системы и вдруг закрыл вкладку).
Ребят, подскажите пожалуйста правильный ли ход мысли, и все ли нормально? Если нет, то подскажите пожалуйста что и как. Буду безумно благодарен.
Есть еще вопрос, как чистить данные каждые 30 дней в базе?
Допустим вот период прошел в 30 дней, у партнера счетчики должны обнуляться, т.е. количество переходов по ссылке, регистраций приглашенных, количество платежей, общая сумма привлеченных средств - но сумма заработанного не должна сбрасываться, если он не заказал выплату средств.
Выплата средств планируется производиться в определенные дни, допустим с 1-5 число каждого месяца. Партнер должен сам заказать выплату в эти дни, если он отправляет заявку на выплату, то мы при отправке заявки ставим в earn_paid 1, т.е. заказ средств произведен и происходит сброс данных в earn_affiliate.
Простой