1) user_data (анонимни/обединени профили)
Това е “мастер” профилът, към който ще прикачваш visitor-и, сесии, форми, покупки и т.н.
| Field | Type | Key | Notes |
|---|---|---|---|
| user_data_id | BIGINT UNSIGNED | PK | AUTO_INCREMENT |
| users_id | INT UNSIGNED | INDEX | NULL ако не е регистриран; при login/регистрация го попълваш |
| first_seen | DATETIME | първо засичане | |
| last_seen | DATETIME | INDEX | последно засичане |
| consent_level | TINYINT UNSIGNED | 0=unknown, 1=essential, 2=analytics, 3=marketing | |
| status | TINYINT UNSIGNED | 1=active, 9=merged/disabled |
users.user_data_idвече сочи към тозиuser_data.user_data_id(което ти решава “преди/след логин”).
2) sessions (сесии, вързани към user_data)
Тук държим минималните данни за сесия: сайт, време, базово geo, устройство.
| Field | Type | Key | Notes |
|---|---|---|---|
| session_id | BIGINT UNSIGNED | PK | AUTO_INCREMENT |
| ses_key | VARBINARY(32) | UNIQUE | random id от cookie/localStorage (не varchar) |
| user_data_id | BIGINT UNSIGNED | INDEX | FK към user_data |
| site_id | INT UNSIGNED | INDEX | мултисайт |
| start_time | DATETIME | INDEX | |
| last_time | DATETIME | INDEX | обновяваш при ping/exit |
| entry_page_n | BIGINT UNSIGNED | първа страница (n) ако имаш | |
| referer_domain_id | INT UNSIGNED | INDEX | нормализиран домейн (виж таблица 3) |
| utm_id | BIGINT UNSIGNED | INDEX | към campaign/utm (може да е NULL за начало) |
| ip4 | INT UNSIGNED | INDEX | IPv4 като INT (0 ако не е налично) |
| geo_id | INT UNSIGNED | INDEX | към geo (виж таблица 4) |
| device_id | BIGINT UNSIGNED | INDEX | към device (виж таблица 5) |
ses_keyе това, което JS праща към PHP (и отива в cookie).session_idе вътрешният PK.
3) ref_domains (нормализирани реферери, без url-и)
Маркетинг анализа ти става лесен: direct/google/facebook/gpt и т.н.
| Field | Type | Key | Notes |
|---|---|---|---|
| referer_domain_id | INT UNSIGNED | PK | AUTO_INCREMENT |
| domain_hash | BINARY(16) | UNIQUE | MD5/UUID-like hash на lower(domain) |
| domain | VARBINARY(128) | punycode/lower; без пълни URL-и | |
| class | TINYINT UNSIGNED | INDEX | 0=unknown,1=direct,2=search,3=social,4=ads,5=ai,6=referral |
4) geo (минимално гео – град/държава)
Тук е “резултатът” от IP lookup (без да пазиш IP range-ове вътре).
| Field | Type | Key | Notes |
|---|---|---|---|
| geo_id | INT UNSIGNED | PK | AUTO_INCREMENT |
| country_id | SMALLINT UNSIGNED | INDEX | ISO numeric или твой ID |
| region_id | INT UNSIGNED | по избор | |
| city_id | INT UNSIGNED | INDEX | по избор |
| tz_offset | SMALLINT | по избор (минути) |
IP range базата може да е отделно и да я ползваш само за lookup, но за “минимален старт” ти трябва само
geo_id.
5) devices (нормализация на устройство/браузър)
Не пазим огромни user-agent-и в sessions; държим fingerprint/хеш.
| Field | Type | Key | Notes |
|---|---|---|---|
| device_id | BIGINT UNSIGNED | PK | AUTO_INCREMENT |
| ua_hash | BINARY(16) | UNIQUE | md5 на user-agent + др. сигнали |
| device_type | TINYINT UNSIGNED | INDEX | 1=desktop,2=mobile,3=tablet,4=bot |
| os_id | SMALLINT UNSIGNED | INDEX | нормализиран ID |
| browser_id | SMALLINT UNSIGNED | INDEX | нормализиран ID |
Какво записваме “при посещение” минимално
В първата версия ти стигат само:
-
user_data(ако няма — създай) -
sessions(всяко ново посещение/нов ses_key)