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

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

За делот полагање на основен испит, почнувам од делот на Нормализација и Функционална зависност.
Правиме анализа на базата во моментот и треба да провериме :
-> Дали секое поле е атомско? (1НФ)
-> Дали сите атрибути зависат целосно од примарниот клуч? (2НФ)
-> Дали има транзитивни зависности? (3НФ) A → B и B → C, тогаш A → C
Моментална ситуација на базата е следна :

При анализа ги следине сите табелиод базата и одлучуваме:
→ Кои полиња зависат од што?
→ Што го одредува што?

• Табела 1 → orders :
  

id_order → payment_method, status, delivery_price, total_price, invoice_code, order_date, id_customer
Ова значи дека сè што се однесува на некоја нарачка,притоа секој атрибут зависи од примарен клуч id_order.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 2 → customers:
  

id_customer → email, first_name, last_name, delivery_address, password, phone
id_customer е примарниот клуч па ги одредува сите други полиња што го опишуваат клиентот.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 3 → delivery:
  

id_delivery, delivery_address, status, id_order
Во оваа табела, id_delivery е примарниот клуч со кој добиваме инфо каде дадена нарачка и каков статус има притоа имаме id_order кој е надворешен клуч и не влијае на зависностите на другите атрибути.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 4 → contains:

quantity, size, price, id_order, id_stock
id_order + id_stock → quantity, size, price
Табелата contains е нормализирана во 2НФ и 3НФ и не треба декомпозиција иако имаме ваков спој за клуч.
Табелата contains го поврзува id_order со id_stock, и секоја нарачка ја означува количината на производот кој е поврзан со нарачката.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 5 → stock:

id_stock, id_product, size, quantity, price
Оваа табела чува податоци за секој продукт за да видиме со што располагаме.
Табелата е во 2НФ, затоа што сите атрибути зависат целосно од примарниот клуч (id_stock).
Заклучок: 1НФ - 2НФ - 3НФ Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 6 → products :

id_product, id_category, product_name, color, price, description, image_url
Табелата е во 2НФ, затоа што сите атрибути зависат целосно од id_product, кој е примарен клуч.
Заклучок: 1НФ - 2НФ - 3НФ
Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

• Табела 7 → categories:

id_category, category_name
Табелата е во 2NF, затоа што сите атрибути зависат целосно од id_category, кој е примарен клуч.

• Клучни точки за базата :
  

orders → customers = еден корисник може да има повеќе нарачки
delivery → orders = секоја достава е поврзана со една нарачка
stock → products = еден артикл во магацин е поврзан за еден производ, но може да има повеќе варијации по големина и боја
contains ја врзува orders и stock = една нарачка може да содржи повеќе артикли, а еден артикл може да биде дел од повеќе нарачки

• Заклучок: 1НФ - 2НФ - 3НФ
  

Табелата е нормализирана до 3НФ и нема потреба од декомпозиција.

Update

Може да забележиме во постоечкиот модел дека имаме јасни ентитети:
customers, products, orders, stock, contains, categories, delivery.
За почеток, што би можеле да подобриме?

Update : Address

Приметуваме дека имаме повторување на delivery_address и во customers и во delivery.
Затоа можеби е подобро да се извлече addresses како посебна табела.

CREATE TABLE addresses (
    id_address INTEGER PRIMARY KEY,
    id_customer INTEGER,
    address TEXT,
    FOREIGN KEY (id_customer) REFERENCES customers(id_customer)
);

Ја додаваме колоната id_address

ALTER TABLE orders ADD COLUMN id_address INTEGER;

Потоа го додаваме надворешниот клуч

ALTER TABLE orders ADD CONSTRAINT fk_orders_address 
FOREIGN KEY (id_address) REFERENCES addresses(id_address);

Истата постапка ја правиме и за delivery
На крај правиме насочување на orders и delivery кон на id_address
наместо да ја чуваат адресата директно.
Ова овозможува еден корисник да има повеќе адреси и да ги избере при checkout.

Наредна измена е кај опцијата price се појавува во products, stock, и contains.
Може да има проблем овде бидејки products.price е основна цена а contains.price
ја претставува цената при купување на производт,кој можеби е на попуст. Затоа, правиме посебна табела discounts

CREATE TABLE discounts (
    id_discount INTEGER PRIMARY KEY,
    id_product INTEGER NOT NULL,
    size TEXT NOT NULL,
    discount_price DECIMAL NOT NULL,
    valid_from DATE,
    valid_to DATE,
    FOREIGN KEY (id_product) REFERENCES products(id_product)
);

А како што може да приметиме, додадовме нова табела која се вика payment_methods.
Ова е со цел наместоorders да чува payment_method како текст (Credit Card,PayPal...)
ние направивме да има id_payment_method (foreign key), додаваме foreign key constraint
па новата payment_methods табелата ги содржи сите можни методи,многу појасна и нормализирана.

Update : Sizes

Оваа табела ја издвоивме како посебна за да не дојде до конфузија и дупликати.
Секоја големина овде има свој id_size, па ке се референцира кај сите други табели.
На овој начин се олеснува додадавање или измена на големини без да се менуваат многу табели.

Update : Contains

Во табелата contains наместо size ставаме id_stock,
значи правиме референца кон друга табела stockкоја го содржи производот и неговата големина.
Ова помага да се избегнат дупликати, бидејки во првиот случај во contains
се претставени како текст S,M,L,XL, некој може да напише "S", друг "s", трет "Small" и тоа создава неконзистентност.
Затоа како посебна табела sizes секоја големина има свој уникатен ID. Табелата stockго поврзува производот id_product со таа големина id_size, а табелата contains ја користи врската преку id_stock. На овој начин имаме едно дефинирање за секоја големина, која се користи унифицирано низ целата база.
Тоа ја прави структурата почиста, полесна за одржување и без грешки.

Update : Stock

Како што веќе знаеме, сега наместо size, користиме id_size од посебна табела sizes.
Нареден чекор како измена е бришење на price од табелата stock,
бидејки може да доведе до конфузија дали цената се одредува на ниво на големина,
а во реалност е дека цената се одредува на ниво на продукт.

Update : Wishlist

Оваа табела нуди нова функционалност на сајтот, посебна страна каде се издвоени посакуваните продукти на корисникот.
Оваа табела можеби нема многу удел во некоја функционалност во база, но придонесува до подобрено корисничко искуство,
со тоа што секој продукт има икона во форма на срце преку која може да издели продукти на посебна страна,
и на крај од сите изделени продукти да одбере оние што ке ги купи.