| | 1 | = Логички и физички дизајн - Креирање база податоци (со SQL DDL) |
| | 2 | == Ознаки |
| | 3 | * Со * се означени надворешни клучеви |
| | 4 | * Со !** се означени втори надворешни клучеви |
| | 5 | * Со !*** се означени трети надворешни клучеви |
| | 6 | * Примарните клучеви се подвлечени и болдирани |
| | 7 | |
| | 8 | == Табели |
| | 9 | === Ентитети |
| | 10 | |
| | 11 | * API_USER(__'''user_id'''__, user_first_name, user_last_name, user_email, user_phone_no, user_password_hash) |
| | 12 | |
| | 13 | * COMPANY(__'''company_id'''__, company_name, company_address) |
| | 14 | |
| | 15 | * RESTAURANT(__'''rest_id'''__, rest_name, rest_email, rest_location, rest_phone, rest_website) |
| | 16 | |
| | 17 | * CATEGORY(__'''cat_id'''__, cat_name, cat_description) |
| | 18 | |
| | 19 | * CONTRACT_STATUS(__'''contract_status_id'''__, contract_status_name) |
| | 20 | |
| | 21 | * DELIVERY_STATUS(__'''d_status_id'''__, d_status_name) |
| | 22 | |
| | 23 | * ORDER_STATUS(__'''o_status_id'''__, o_status_name) |
| | 24 | |
| | 25 | * EMPLOYEE_LOYALTY_STATUS(__'''emp_loyalty_status_id'''__, emp_loyalty_status_name) |
| | 26 | |
| | 27 | * INGREDIENT(__'''ingr_id'''__, ingr_name) |
| | 28 | |
| | 29 | * ALLERGEN(__'''allergen_id'''__, allergen_name, allergen_description) |
| | 30 | |
| | 31 | * LOYALTY_TIER(__'''tier_id'''__, tier_name, tier_minimum_points, tier_maximum_points, tier_discount_percentage, tier_free_delivery_eligibility, tier_priority_support, tier_created_at, tier_update_at) |
| | 32 | |
| | 33 | * REVIEW(__'''review_id'''__, review_comment, review_created_at, review_rating) |
| | 34 | |
| | 35 | * COMPANY_EMPLOYEE(__'''user_id*'''__, company_id!**) |
| | 36 | * * референцира до API_USER (user_id) |
| | 37 | * !** референцира до COMPANY (company_id) |
| | 38 | |
| | 39 | * ADMIN(__'''user_id*'''__) |
| | 40 | * * референцира до API_USER (user_id) |
| | 41 | |
| | 42 | * CUSTOMER(__'''user_id*'''__) |
| | 43 | * * референцира до API_USER (user_id) |
| | 44 | |
| | 45 | * DRIVER(__'''user_id*'''__) |
| | 46 | * * референцира до API_USER (user_id) |
| | 47 | |
| | 48 | * CONTRACT(__'''contract_id'''__, company_id*, rest_id!**, contract_status_id!***, contract_start_date, contract_end_date) |
| | 49 | * * референцира до COMPANY (company_id) |
| | 50 | * !** референцира до RESTAURANT (rest_id) |
| | 51 | * !*** референцира до CONTRACT_STATUS (contract_status_id) |
| | 52 | |
| | 53 | * DELIVERY(__'''delivery_id'''__, delivery_date, delivery_notes, d_status_id*, driver_user_id!**) |
| | 54 | * * референцира до DELIVERY_STATUS (d_status_id) |
| | 55 | * !** референцира до DRIVER (user_id) |
| | 56 | |
| | 57 | * COMPANY_ORDER(__'''comp_order_id'''__, company_id*, delivery_id!**) |
| | 58 | * * референцира до COMPANY (company_id) |
| | 59 | * !** референцира до DELIVERY (delivery_id) |
| | 60 | |
| | 61 | * LUNCH_TIME(__'''lunch_time_id'''__, comp_order_id*, contract_id!**, lunch_start, lunch_end, lunch_preorder_offset, lunch_weekday) |
| | 62 | * * референцира до COMPANY_ORDER (comp_order_id) |
| | 63 | * !** референцира до CONTRACT (contract_id) |
| | 64 | |
| | 65 | * MEAL(__'''meal_id'''__, cat_id*, rest_id!**, meal_name, meal_description, meal_price, meal_weight) |
| | 66 | * * референцира до CATEGORY (cat_id) |
| | 67 | * !** референцира до RESTAURANT (rest_id) |
| | 68 | |
| | 69 | * DRINK(__'''drink_id'''__, rest_id*, drink_name, drink_milliliters, drink_price) |
| | 70 | * * референцира до RESTAURANT (rest_id) |
| | 71 | |
| | 72 | * CUSTOMER_ORDER(__'''order_id'''__, comp_order_id*, company_employee_user_id!**, order_datetime, o_status_id!***, order_total) |
| | 73 | * * референцира до COMPANY_ORDER (comp_order_id) |
| | 74 | * !** референцира до COMPANY_EMPLOYEE (user_id) |
| | 75 | * !*** референцира до ORDER_STATUS (o_status_id) |
| | 76 | |
| | 77 | * INVOICE(__'''invoice_id'''__, comp_order_id*) |
| | 78 | * * референцира до COMPANY_ORDER (comp_order_id) |
| | 79 | |
| | 80 | * EMPLOYEE_LOYALTY(__'''emp_loyalty_id'''__, user_id*, emp_loyalty_curr_points, emp_loyalty_joined_at, emp_loyalty_status_id!**, tier_id!***) |
| | 81 | * * референцира до COMPANY_EMPLOYEE (user_id) |
| | 82 | * !** референцира до EMPLOYEE_LOYALTY_STATUS (emp_loyalty_status_id) |
| | 83 | * !*** референцира до LOYALTY_TIER (tier_id) |
| | 84 | |
| | 85 | * ORDER_REVIEW(__'''review_id*'''__, order_id!**, order_review_food_rating, order_review_res_rating) |
| | 86 | * * референцира до REVIEW (review_id) |
| | 87 | * !** референцира до CUSTOMER_ORDER (order_id) |
| | 88 | |
| | 89 | * DELIVERY_REVIEW(__'''review_id*'''__, delivery_id!**, del_review_courier_rating, del_review_speed_rating) |
| | 90 | * * референцира до REVIEW (review_id) |
| | 91 | * !** референцира до DELIVERY (delivery_id) |
| | 92 | |
| | 93 | ==== Релациски табели |
| | 94 | |
| | 95 | * MEAL_INGREDIENT(__'''meal_id*, ingr_id!**'''__) |
| | 96 | * * референцира до MEAL (meal_id) |
| | 97 | * !** референцира до INGREDIENT (ingr_id) |
| | 98 | |
| | 99 | * ALLERGEN_INGREDIENT(__'''allergen_id*, ingr_id!**'''__) |
| | 100 | * * референцира до ALLERGEN (allergen_id) |
| | 101 | * !** референцира до INGREDIENT (ingr_id) |
| | 102 | |
| | 103 | * ORDER_MEAL(__'''order_id*, meal_id!**'''__, quantity) |
| | 104 | * * референцира до CUSTOMER_ORDER (order_id) |
| | 105 | * !** референцира до MEAL (meal_id) |
| | 106 | |
| | 107 | * ORDER_DRINK(__'''order_id*, drink_id!**'''__, quantity) |
| | 108 | * * референцира до CUSTOMER_ORDER (order_id) |
| | 109 | * !** референцира до DRINK (drink_id) |
| | 110 | |
| | 111 | == DDL скрипта за креирање на табелите |
| | 112 | === Скрипта за креирање на шема |
| | 113 | [attachment:ddl.sql] |
| | 114 | |
| | 115 | == ЕР Дијаграм |
| | 116 | [[Image(kbnteam_ERDiagram.jpg)]] |
