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

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

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

Role (role_id, name UNIQUE)

Category (category_id, name UNIQUE)

Status (status_id, name UNIQUE)

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

Package (package_id, name, max_usage, total_price)

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), points_awarded)

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

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

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 скрипта за бришење на табелите и креирање на табелите

schema_creation.sql​

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

data_load.sql​

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