wiki:Normalization

Нормализација и подобрување на дизајн

Содржина

  1. Определување функционални зависности
    1. Функционални зависности
      1. Users
      2. Customer го проширува User
      3. Manager го проширува User
      4. Driver го проширува User
      5. City
      6. Region
      7. Warehouse
      8. Vehicle
      9. Categories
      10. Manufacturer
      11. Article
      12. Price
      13. Pro forma
      14. Pro Forma Status
      15. Delivery
      16. Delivery Status
      17. Order
      18. Delivery Status
      19. Article unit
      20. Weekday
      21. Customer weekday
      22. Token
      23. Image store
    2. Класификација на атрибути
      1. Лево - атрибути кои одредуваат други
      2. Лево и десно - атрибути кои одредуваат други и се одредени од други
      3. Десно - атрибути кои се одредени од други
    3. Покривачи на примарните клучеви
      1. Vehicle
      2. User
      3. Manufacturer
      4. Article
      5. Price
      6. Pro-forma
      7. Delivery
      8. Order
      9. Article Unit
      10. Композитен клуч - Article Unit и Price
      11. Weekday
      12. Token
      13. Image Store
    4. Анализа според покривачи
  2. Проблеми и подобрување на дизајн

Определување функционални зависности

R = {user_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, city_name, region_id, region_name, cust_EDB, cust_company_name, cust_adr, cust_representative_img, wh_id, wh_adr, veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin,veh_reg_date, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email, art_id, art_name, art_image, art_weight, price_id, price, price_eff_date, pf_id, pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc, del_id, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, d_status_name, d_status_desc, ord_id, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, unit_id, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, day_id, day_name, cust_day_id, start_time, end_time, t_id, t_value, t_date, t_type, t_expiry, t_validated_at, t_user, img_id, img_path, img_ent_type, img_ent_id}

Функционални зависности

Users

user_id → user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id

Customer го проширува User

user_id → cust_EDB, cust_company_name, cust_adr, cust_representative_img (каде што clazz_ = 'customer')

Manager го проширува User

user_id → wh_id (каде што clazz_ = 'manager')

Driver го проширува User

user_id → veh_id (каде што clazz_ = 'driver')

City

city_id → city_name, region_id

Region

region_id → region_name

Warehouse

wh_id → wh_adr, city_id

Vehicle

veh_id → veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id

Categories

ctg_id → ctg_name

Manufacturer

man_id → man_name, man_adr, man_mobile, man_email

Article

art_id → art_name, art_image, art_weight, ctg_id, man_id

Price

price_id → price, price_eff_date, art_id

Pro forma

pf_id → pf_deadline, pf_date_created, pf_status_id

Pro Forma Status

pf_status_id → pf_status_name, pf_status_desc

Delivery

del_id → del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, veh_id

Delivery Status

d_status_id → d_status_name, d_status_desc

Order

ord_id → ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, cust_id, del_id, pf_id

Delivery Status

o_status_id → o_status_name, o_status_desc

Article unit

unit_id → unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, ord_id

Weekday

day_id → day_name

Customer weekday

cust_day_id → cust_id, day_id, start_time, end_time

Token

t_id → t_value, t_date, t_type, t_expiry, t_validated_at, t_user

Image store

img_id → img_path, img_ent_type, img_ent_id

Класификација на атрибути

Лево - атрибути кои одредуваат други

cust_day_id, price_id, pf_id, del_id, ord_id, unit_id, t_id, img_id

Лево и десно - атрибути кои одредуваат други и се одредени од други

user_id, city_id, region_id, wh_id, veh_id, ctg_id, man_id, art_id, d_status_id, o_status_id, pf_status_id, day_id

Десно - атрибути кои се одредени од други

user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_name, region_name, cust_EDB, cust_company_name, cust_adr, cust_representative_img, wh_adr, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, ctg_name, man_name, man_adr, man_mobile, man_email, art_name, art_image, art_weight, price, price_eff_date, pf_deadline, pf_date_created, pf_status_name, pf_status_desc, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_name, d_status_desc, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_name, o_status_desc, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, day_name, start_time, end_time, t_value, t_date, t_type, t_expiry, t_validated_at, t_user, img_path, img_ent_type, img_ent_id

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

Vehicle

veh_id+ = {veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr, city_id, city_name, region_id, region_name}

User

user_id+ = {user_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, city_name, region_id, region_name}

  • Ако тип на корисник е 'customer':

user_id+ += {cust_EDB, cust_company_name, cust_adr, cust_representative_img}

  • Ако тип на корисник е 'manager':

user_id+ += {wh_id, wh_adr}

  • Ако тип на корисник е 'driver':

user_id+ += {veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr}

Manufacturer

man_id+ = {man_id, man_name, man_adr, man_mobile, man_email}

Article

art_id+ = {art_id, art_name, art_image, art_weight, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email}

Price

price_id+ = {price_id, price, price_eff_date, art_id, art_name, art_image, art_weight, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email}

Pro-forma

pf_id+ = {pf_id, pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc}

Delivery

del_id+ = {del_id, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, d_status_name, d_status_desc, veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr, city_id, city_name, region_id, region_name}

Order

ord_id+ = {ord_id, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, cust_id, del_id, pf_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, cust_EDB, cust_company_name, cust_adr, cust_representative_img, city_name, region_id, region_name, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, veh_id, d_status_name, d_status_desc, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr,pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc}

Article Unit

unit_id+ = {unit_id, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, wh_adr, city_id, city_name, region_id, region_name, ord_id}

  • Ако ord_id не е null, исто така ги вклучува и:

unit_id+ += {ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, cust_id, del_id, pf_id,

и сите атрибути одредени од ord_id

}

Композитен клуч - Article Unit и Price

(unit_id, price_id)+ = {unit_id, price_id,

Сите атрибути определни од unit_id

unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, ord_id,

Сите атрибути определни од price_id

price, price_eff_date, art_id,

Сите транзитивни атрибути

wh_adr, city_id, city_name, region_id, region_name, art_name, art_image, art_weight, ctg_id, man_id, ctg_name, man_name, man_adr, man_mobile, man_email}

Weekday

cust_day_id+ = {cust_day_id, cust_id, day_id, start_time, end_time,

Од day_id:

day_name,

Од cust_id:

user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, cust_EDB, cust_company_name, cust_adr, cust_representative_img,

Од city_id:

city_name, region_id, region_name}

Token

t_id+ = {t_id, t_value, t_date, t_type, t_expiry, t_validated_at, t_user,

Од t_user (кое е user_id):

user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id,

Од city_id:

city_name, region_id, region_name}

Image Store

img_id+ = {img_id, img_path, img_ent_type, img_ent_id}

Анализа според покривачи

Од анализирање на покривачите може да ги забележиме следните проблеми во нормализацијата на базата:

  • Транзитивни зависности во податоците за возила: veh_id → veh_last_service, veh_last_service_km
  • Недостига зависноста (ord_id, art_id) → quantity, price
  • Историја на цени и вредност - unit_id → unit_cost_price
  • Поврзување на слики: img_id → img_ent_type, img_ent_id

Проблеми и подобрување на дизајн

Last modified 2 days ago Last modified on 08/29/25 15:56:05
Note: See TracWiki for help on using the wiki.