Логички и физички дизајн

Релациска шема (со парцијално мапирање)

• примарни клучеви - болдирани и подвлечени
• надворешни клучеви - * до името на надворешниот клуч и во заграда табелата кон која референцира
• not null атрибути: bold
• Останати атрибути: без ознака

---

• _user (id, name, email, login_provider, password_hash, is_verified, is_profile_complete, has_used_free_consultation, company_size, work_position, points)
• user_favorite_course_ids (user*(USER), favorite_course_ids)
• verification_token (uuid, created_at, expires_at, user_id*(USER))
• expert (id, name, email, login_provider, password_hash)
• enrollment (id, enrollment_status, purchase_date, activation_date, completion_date, course_version_id* (COURSE VERSION), user_id* (USER))
• payment(id, amount, payment_date, payment_method, payment_status, enrollment_id*(ENROLLMENT))
• review (id, rating, comment, review_date, enrollment_id*(ENROLLMENT))
• course_version (id, version_number, created_at, is_active, course_id*(COURSE))
• course (id, image_url, color, difficulty, duration_minutes, price)
• course_translate (id, language, title_short, title, description_short, description, description_long, course_id*(COURSE))
• course_translate_what_will_be_learned (course_id*(COURSE), what_will_be_learned)
• course_content (id, position, course_version_id*(COURSE VERSION))
• course_content_translate (id, title, language, course_content_id*(COURSE CONTENT))
• course_lecture (id, duration_minutes, position, content_type, course_content_id*(COURSE CONTENT))
• course_lecture_translate (id, language, content_file_name, title, description, content_text, course_lecture_id*(COURSE LECTURE))
• tag (id, type)
• tag_translate (id, language, value, tag_id*(TAG))
• course_tag (course_id*(COURSE), tag_id*(TAG))
• user_tag (user_id*(USER), tag_id*(TAG))
• user_course_progress (id, is_completed, completed_at, enrollment_id*(ENROLLMENT), course_lecture_id*(COURSE LECTURE))

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

schema_creation.sql​

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

data_load.sql​

Релациски дијаграм изведен од yFiles (IntelliJ)