Релациска шема со мапирачка трансформација

• Примарни клучеви означени со underline
• Надворешни клучеви означени со * и табела кон која покажува
• Not null атрибути означени со bold

User (user_id, full_name, email, phone, created_at)

Role (role_id, name)

Category (category_id, name)

Status (status_id, name)

Service (service_id, name, price, duration_minutes, category_id*(Category))

Package (package_id, name, max_usage)

Review (review_id, rating, comment, created_at, payment_id*(Payment) UNIQUE)

Appointment (appointment_id, appointment_time, end_time, notes, type enum('pre-booked','walk-in'), total_price, user_id*(User), status_id*(Status))

LoyaltyCard (card_id, points, user_id*(User))

Payment (payment_id, amount, method, timestamp, status, appointment_id*(Appointment), points_used)

UserRole (user_id*(User), role_id*(Role))

PackageService (package_id*(Package), service_id*(Service), discounted_price)

AppointmentService (appointment_id*(Appointment), service_id*(Service))

Availability (availability_id, date, start_time, end_time, is_closed)

UserPackagePurchase (purchase_id, *user_id(User), *package_id(Package),purchased_at, total_uses, remaining_uses, status, expires_at)

AppointmentPackageUsage (appointment_id*(Appointment), purchase_id*(UserPackagePurchase), service_id*(Service), used_units, finalized_at)

DDL скрипта за бришење на табелите и креирање на табелите

create.sql​

DML скрипта за полнење на табелите со податоци

populate.sql​

Релациски дијаграм изваден од DBeaver