Changes between Initial Version and Version 1 of Normalization

Timestamp:

05/24/26 17:14:45 (35 hours ago)

Author:

202033

Comment:

--

Normalization

@@ +1 @@
+= P5: Нормализација =
+
+== 1. Почетна де-нормализирана релација ==
+
+Почетно се разгледува една глобална де-нормализирана релација со уникатни имиња на атрибути:
+
+{{{
+R(
+u_user_id, u_full_name, u_email, u_phone, u_created_at, u_password_hash,
+ur_user_id, ur_role_id, r_role_name,
+lc_card_id, lc_user_id, lc_points,
+a_appointment_id, a_appointment_time, a_end_time, a_notes, a_type,
+a_total_price, a_points_awarded, a_user_id, a_status_id, st_status_name,
+aps_appointment_id, aps_service_id,
+s_service_id, s_service_name, s_price, s_duration_minutes,
+s_category_id, c_category_id, c_category_name,
+p_payment_id, p_amount, p_method, p_timestamp, p_status,
+p_appointment_id, p_points_used, p_package_purchase_id,
+rv_review_id, rv_rating, rv_comment, rv_created_at, rv_payment_id,
+pkg_package_id, pkg_name, pkg_max_usage, pkg_total_price,
+ps_package_id, ps_service_id, ps_discounted_price,
+upp_purchase_id, upp_user_id, upp_package_id,
+upp_purchased_at, upp_total_uses, upp_remaining_uses,
+upp_status, upp_expires_at,
+apu_appointment_id, apu_purchase_id, apu_service_id,
+apu_used_units, apu_finalized_at,
+av_availability_id, av_date, av_start_time,
+av_end_time, av_is_closed
+)
+}}}
+
+Во оваа форма податоците од повеќе независни ентитети и M:N релации се споени во една глобална релација, што создава:
+
+ * повторување на податоци,
+ * update/delete аномалии,
+ * мултивредносни групи,
+ * зависности меѓу атрибути што не се дел од ист ентитет.
+
+Глобалната релација концептуално ги опфаќа следните ентитети и релации:
+
+ * `"User"`
+ * `role`
+ * `userrole`
+ * `category`
+ * `service`
+ * `status`
+ * `appointment`
+ * `appointmentservice`
+ * `payment`
+ * `review`
+ * `package`
+ * `packageservice`
+ * `userpackagepurchase`
+ * `appointmentpackageusage`
+ * `availability`
+ * `loyaltycard`
+
+== 2. Функционални зависности ==
+
+{{{
+"User".user_id
+    -> full_name, email, phone, created_at, password_hash
+
+"User".email
+    -> user_id, full_name, phone, created_at, password_hash
+
+role.role_id
+    -> role.name
+
+category.category_id
+    -> category.name
+
+status.status_id
+    -> status.name
+
+service.service_id
+    -> service.name, service.price,
+       service.duration_minutes, service.category_id
+
+appointment.appointment_id
+    -> appointment_time, end_time,
+       notes, type, total_price,
+       user_id, status_id, points_awarded
+
+payment.payment_id
+    -> amount, method, timestamp,
+       status, appointment_id,
+       points_used, package_purchase_id
+
+review.review_id
+    -> rating, comment, created_at, payment_id
+
+package.package_id
+    -> name, max_usage, total_price
+
+userpackagepurchase.purchase_id
+    -> user_id, package_id,
+       purchased_at, total_uses,
+       remaining_uses, status, expires_at
+
+availability.availability_id
+    -> date, start_time, end_time, is_closed
+
+(packageservice.package_id, packageservice.service_id)
+    -> discounted_price
+}}}
+
+Дополнително:
+
+{{{
+(userrole.user_id, userrole.role_id)
+    -> /
+
+(appointmentservice.appointment_id,
+ appointmentservice.service_id)
+    -> /
+}}}
+
+Овие релации немаат не-клучни атрибути.
+
+== 3. Кандидат-клучеви и избор на примарен клуч ==
+
+Во глобалната де-нормализирана релација не постои единечен атрибут што ги одредува сите останати атрибути, бидејќи се комбинирани повеќе независни repeating groups:
+
+ * повеќе улоги по корисник,
+ * повеќе услуги по термин,
+ * повеќе услуги по пакет,
+ * повеќе package purchases,
+ * повеќе availability windows,
+ * повеќе payments и reviews.
+
+Поради тоа, глобалниот кандидат-клуч мора да биде композитен.
+
+Теоретски кандидат-клуч:
+
+{{{
+K_R =
+(
+ur_user_id,
+ur_role_id,
+aps_appointment_id,
+aps_service_id,
+p_payment_id,
+ps_package_id,
+ps_service_id,
+upp_purchase_id,
+av_availability_id
+)
+}}}
+
+Почетната глобална релација не ја задоволува 1НФ и се наоѓа во UNF форма.
+
+== 4. Декомпозиција до 1НФ ==
+
+=== Релација што се анализира ===
+
+{{{
+R
+}}}
+
+=== Проблем ===
+
+Во глобалната релација постојат повторувачки групи и мултивредносни атрибути:
+
+ * повеќе улоги по корисник,
+ * повеќе услуги по термин,
+ * повеќе услуги по пакет,
+ * повеќе package purchases,
+ * повеќе availability windows.
+
+=== Декомпозиција ===
+
+{{{
+"User"(user_id, full_name, email, phone, created_at, password_hash)
+
+role(role_id, name)
+
+userrole(user_id, role_id)
+
+category(category_id, name)
+
+service(service_id, name, price,
+duration_minutes, category_id)
+
+status(status_id, name)
+
+appointment(
+appointment_id,
+appointment_time,
+end_time,
+notes,
+type,
+total_price,
+user_id,
+status_id,
+points_awarded
+)
+
+appointmentservice(
+appointment_id,
+service_id
+)
+
+payment(
+payment_id,
+amount,
+method,
+timestamp,
+status,
+appointment_id,
+points_used,
+package_purchase_id
+)
+
+review(
+review_id,
+rating,
+comment,
+created_at,
+payment_id
+)
+
+package(
+package_id,
+name,
+max_usage,
+total_price
+)
+
+packageservice(
+package_id,
+service_id,
+discounted_price
+)
+
+userpackagepurchase(
+purchase_id,
+user_id,
+package_id,
+purchased_at,
+total_uses,
+remaining_uses,
+status,
+expires_at
+)
+
+appointmentpackageusage(
+appointment_id,
+purchase_id,
+service_id,
+used_units,
+finalized_at
+)
+
+availability(
+availability_id,
+date,
+start_time,
+end_time,
+is_closed
+)
+
+loyaltycard(
+card_id,
+points,
+user_id
+)
+}}}
+
+=== Клучеви ===
+
+Едноставни PK:
+
+{{{
+user_id
+role_id
+category_id
+service_id
+status_id
+appointment_id
+payment_id
+review_id
+package_id
+purchase_id
+availability_id
+card_id
+}}}
+
+Композитни PK:
+
+{{{
+userrole(user_id, role_id)
+
+appointmentservice(
+appointment_id,
+service_id
+)
+
+packageservice(
+package_id,
+service_id
+)
+}}}
+
+=== Lossless Join ===
+
+Декомпозицијата е lossless бидејќи сите релации се поврзани преку PK/FK врски.
+
+=== Dependency Preservation ===
+
+Функционалните зависности се зачувани во новите релации.
+
+== 5. Декомпозиција до 2НФ ==
+
+=== Релации што се анализираат ===
+
+ * `userrole`
+ * `appointmentservice`
+ * `packageservice`
+
+=== Проверка ===
+
+Во `userrole` и `appointmentservice` нема не-клучни атрибути.
+
+Во `packageservice`:
+
+{{{
+(package_id, service_id)
+    -> discounted_price
+}}}
+
+`discounted_price` зависи од целата комбинација, а не од дел од клучот.
+
+=== Заклучок ===
+
+Постојната шема веќе ја задоволува 2НФ.
+
+Нема потреба од дополнителна декомпозиција.
+
+== 6. Декомпозиција до 3НФ ==
+
+=== Транзитивни зависности ===
+
+{{{
+service.service_id
+    -> service.category_id
+    -> category.name
+
+appointment.appointment_id
+    -> appointment.status_id
+    -> status.name
+
+"User".user_id
+    -> userrole.role_id
+    -> role.name
+}}}
+
+=== Решение ===
+
+Транзитивните зависности се отстранети преку издвојување на lookup релации:
+
+ * `category`
+ * `status`
+ * `role`
+
+Дополнително:
+
+ * `review` е одвоена од `payment`
+ * `payment` е одвоена од `appointment`
+ * `package` е одвоена од `userpackagepurchase`
+
+=== Заклучок ===
+
+Релациите ја задоволуваат 3НФ.
+
+== 7. Проверка за BCNF ==
+
+Во најголем дел релациите ја задоволуваат BCNF бидејќи детерминантите се кандидат-клучеви.
+
+Особено:
+
+ * `"User"`
+ * `role`
+ * `category`
+ * `status`
+ * `service`
+ * `appointment`
+ * `payment`
+ * `review`
+ * `package`
+ * `userpackagepurchase`
+ * `availability`
+ * `loyaltycard`
+
+Контролирани де-нормализирани атрибути:
+
+ * `appointment.total_price`
+ * `package.total_price`
+ * `payment.amount`
+ * `appointment.points_awarded`
+
+Овие атрибути се свесно задржани за:
+
+ * перформанси,
+ * историска конзистентност,
+ * поедноставување на трансакциската логика.
+
+Конзистентноста се одржува преку DB-first логика:
+
+=== Functions / Procedures ===
+
+{{{
+fn_recalculate_appointment
+fn_validate_appointment
+sp_create_appointment
+sp_apply_package_to_appointment
+sp_create_payment
+sp_mark_payment_paid
+sp_award_loyalty_points
+}}}
+
+=== Triggers ===
+
+{{{
+appointmentservice_recalculate
+appointment_validate
+payment_validate
+appointment_prevent_price_change
+}}}
+
+== 8. Финален нормализиран модел ==
+
+Финалниот нормализиран модел е составен од:
+
+{{{
+"User"
+role
+userrole
+category
+service
+status
+appointment
+appointmentservice
+payment
+review
+package
+packageservice
+userpackagepurchase
+appointmentpackageusage
+availability
+loyaltycard
+}}}
+
+== 9. Споредба со Phase 2 дизајнот ==
+
+Нормализацијата покажува дека тековниот модел веќе е во голем дел усогласен со 3НФ/BCNF.
+
+Во постојниот дизајн:
+
+ * ентитетите се раздвоени,
+ * M:N релациите се реализирани преку junction табели,
+ * lookup податоците се издвоени,
+ * транзитивните зависности се отстранети,
+ * business logic е имплементирана во базата.
+
+=== Дали е потребно реструктурирање? ===
+
+Не е потребно значајно реструктурирање на базата.
+
+Опционални подобрувања:
+
+ * конзистентно именување на табели,
+ * дополнително стандардизирање на status вредности.
+
+== 10. Заклучок ==
+
+Финалниот дизајн:
+
+ * ја намалува редундантноста,
+ * избегнува insert/update/delete аномалии,
+ * ги зачувува функционалните зависности,
+ * овозможува lossless join,
+ * и поддржува DB-first имплементација преку SQL функции, процедури, тригери и views