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

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

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

---

• account (id, email, password_hash, name)
• _user (user_id, name, email, password_hash, login_provider, is_verified, is_profile_complete, has_used_free_consultation, work_position, company_size, points, account_id*(ACCOUNT))
• expert (expert_id, account_id*(ACCOUNT))
• language (id, value)
• course (course_id, image_url, color, difficulty, duration_minutes, price)
• course_version (course_version_id, version_number, created_at, is_active, course_id*(COURSE))
• course_translate (course_translate_id, title_short, title, description_short, description, description_long, what_will_be_learned, course_id*(COURSE), language_id*(LANGUAGE))
• course_content (course_content_id, position, course_version_id*(COURSE VERSION))
• course_content_translate (course_content_translate_id, title, course_content_id*(COURSE CONTENT), language_id*(LANGUAGE))
• course_lecture (course_lecture_id, position, duration_minutes, content_type, course_content_id*(COURSE CONTENT))
• course_lecture_translate (course_lecture_translate_id, content_file_name, content_text, title, description, course_lecture_id*(COURSE LECTURE), language_id*(LANGUAGE))
• enrollment (enrollment_id, enrollment_status, purchase_date, activation_date, completion_date, user_id*(USER), course_version_id*(COURSE VERSION))
• payment (payment_id, amount, payment_date, payment_method, payment_status, enrollment_id*(ENROLLMENT))
• review (review_id, rating, comment, review_date, enrollment_id*(ENROLLMENT))
• user_course_progress (user_course_progress_id, is_completed, completed_at, enrollment_id*(ENROLLMENT), course_lecture_id*(COURSE LECTURE))
• verification_token (verification_token_uuid, created_at, expires_at, user_id*(USER))
• tag (tag_id, tag_type)
• tag_translate (tag_translate_id, value, tag_id*(TAG), language_id*(LANGUAGE))
• course_tag (course_id*(COURSE), tag_id*(TAG))
• user_tag (user_id*(USER), tag_id*(TAG))
• user_favorite_course (user_id*(USER), course_id*(COURSE))
• meeting_email_reminder (id, meeting_at, scheduled_at, sent, meeting_link, user_id*(USER))

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

ddl.sql​

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

dml.sql​

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