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