= 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