DatabaseCreation: MajStore

Ознаки

Примарните клучеви се означени со bold

Надворешните клучеви се означени со FK* за кои во заграда () е напишана табелата која ја референцираат.

Табели

Пермисии

Permission (permission_id, permission_name, description)

Role (role_id, role_name)

Role_Permission (role_permission_id, role_id*(Role), permission_id*(Permission))

Корисници

User (user_id, first_name, last_name, username, email, password_hash)

User_Role (user_role_id, role_id*(Role), user_id*(User))

---

Notification (notification_id, message, is_read, created_at, user_id*(User))

Location (location_id, city, region, address)

Мајстори

Worker (worker_id, bio, works_remote, location_id*(Location), user_id*(User))

Calendar (calendar_id, worker_id*(Worker))

Day (day_id, day_name)

Work_Day (work_day_id, start_time, end_time, calendar_id*(Calendar), day_id*(Day))

Calendar_Event (event_id, title, description, start_time, end_time, calendar_id*(Calendar))

Certificate (certificate_id, certificate_name, issuer, worker_id*(Worker))

Specialty (specialty_id, specialty_name)

Worker_Specialty (worker_specialty_id, worker_id*(Worker), specialty_id*(Specialty))

Постови

Post_Status (status_id, status_name)

Post (post_id, title, description, created_at, user_id*(User), location_id*(Location), status_id*(Post_Status))

Post_Image (image_id, image_file, post_id*(Post))

Post_Specialty (post_specialty_id, post_id*(Post), specialty_id*(Specialty))

Апликации

Application_Status (status_id, status_name)

Application (application_id, message, needed_time, created_at, worker_id*(Worker), post_id*(Post), status_id*(Application_Status))

Рецензии

Review (review_id, grade, created_at, reviewer_id*(User), worker_specialty_id*(Worker_Specialty))

Review_Comment (comment_id, review_id*(Review), reply)

Премиум планови

Payment_Method (method_id, method_name, max_amount, tax)

Worker_Method (worker_method_id, worker_id*(Worker), method_id*(Payment_Method))

Payment_Status (status_id, status_name)

Premium_Plan (plan_id, name, price, duration_days, description)

Payment (payment_id, amount, payment_date, worker_method_id*(Worker_Method), payment_status*(Payment_Status), plan_id*(Premium_Plan))

Premium_Worker (premium_id, start_date, end_date, plan_id*(Premium_Plan), worker_id*(Worker), payment_id*(Payment))

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

https://develop.finki.ukim.mk/projects/MajStore/attachment/wiki/DatabaseCreation/MajStore_DDL.sql

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

https://develop.finki.ukim.mk/projects/MajStore/attachment/wiki/DatabaseCreation/MajStore_DDL_delete.sql

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

https://develop.finki.ukim.mk/projects/MajStore/attachment/wiki/DatabaseCreation/MajStore_DML.sql