Privind schema unei aplicații la care lucram mi-am dat seama ca nu aveam niciun foreign key. Intr-adevăr in ultimul timp nu am mai apelat la asa ceva. Nu îmi este clar de ce, posibil din lipsa unor avantaje fara de care nu poti trai.
Intrebare: voi mai folositi foreign key-uri? Daca da de ce?
Observație: folosesc mysql (5.7)
Uhmm… constraints, on update, on delete? Cascade, set null, etc? Nu le apelezi per-se, dar tot beneficiezi de ele.
Avantajul principal e legat de mentinerea consistentei (C din ACID) in baza de date. Daca ai un camp ProductId in tabela Orders, vrei ca el sa pointeze spre un row din Products care chiar exista, nu spre ceva aiurea.
Nu… Exemplul este unul gresit si duce la confuzie.
Asta ca sa nu zic, poti avea mai multe produse la acelasi order.
Pentru ca atunci, ce faci cand stergi un produs si este legat de un order, ce se intampla? ON DELETE SET NULL sau? Ce o sa vada clientul in istoricul comenzilor?
Corect ar fi sa ai o tabela order_items si acolo sa ai atributele produselor din momentul plasarii comenzii. Iar legatura ar fi order_id -> ON DELETE CASCADE, pentru ca… daca stergi orderul, nu prea mai ai nevoie de item-urile asociate.
ce faci cand stergi un produs si este legat de un order, ce se intampla?
Pentru cazul asta ai folosi soft delete - coloana datetime default null.
Comentariul era pt Horia…
Nu inteleg, care-i legatura cu datetime?
Trebuie sa elaborezi totusi afirmatia asta. Ce anume e gresit? Ce anume duce la confuzie?
De acord ca nu-i un exemplu realist, dar eu zic ca am capturat esenta problemei. Poti inlocui produse si ordine cu comentarii si articole sau angajati si unitatea la care lucreaza etc.
IMO, dintr-o baza de date nu ar trebui sa stergi cu delete decat cand faci vre-un fel de cleanup in urma unei erori, sau a unui abuz, sau ca parte a unei migratii in care nu mai e nevoie de datele respective. Dar “stergerea” unui utilizator, a unui produs, sau a oricarei entitati la nivel de aplicatie ar trebui facuta ca “soft delete”, cum spune @victorelu, dupa regulile aplicatiei. Adica doar o marchezi cumva ca stearsa si ti cont de asta in aplicatie, dar nu o stergi fizic. Mie imi place sa folosesc un camp state care e setat pe Archived cand se intampla o stergere, dar si un camp datetime nullable ce e setat la momentul stergerii e o solutie OK.
Motivul principal e ca iti creezi o groaza de probleme cu entitatile legate cat si la nivel de aplicatie. Exemplul tau ilustreaza bine problema asta. Dar solutia propusa - se denormalizezi baza de date mult nu mi se pare OK. Stochezi o groaza de date in plus - imagineaza-ti daca Amazon ar stoca pe fiecare ordin volumul imens de detalii despre un produs pe care il au ei? Si apoi cand vrei sa faci un update lucrurile iarasi se complica - daca schimbi descrierea unui produs si vrei ca utilizatorul sa vada asta in ordinul lui, trebuie sa mergi prin toate ordinele sa faci update-ul. Trebuie pur si simplu ca aplicatia sa fie constienta de idea de entitate “stearsa” de utilizator dar care inca face parte din sistem. De exemplu, in eMag, daca ai un ordin cu un produs care nu mai face parte din catalogul lor, o sa fie marcat lucrurl asta explicit.
Ok, hai sa vedem…
Adica ON UPDATE si ON DELETE, de acord.
ProductId in Orders " presupune ca un order nu poate avea mai multe produse.
Ajungi la o tabela de legatura, eu i-am zis order_items unde pe langa ProductId si OrderId ai avea pret, cantiate si atributele pe care vrei sa le arati despre produsul acela atunci cand o comanda este vazuta.
Sa vedem, qty_available in product_stock (alta tabela) ar fi 100, iar pe order_items qty ar fi 1.
price in “Products” ar fi 100 azi, in order_items ar fi poate 50 ca l-am luat la reducere (fara a aplica cupoane); si mai am alt produs cu qty 4 si price 100 (ca asta nu mai este redus).
Si daca vrei sa pastrezi denumirea de price, in “Orders” ar fi totalul → am 2 produse in order_items cu qty 1 si 4, deci 1 * 50 + 4 * 100 adica 450.
Si hai sa mai vedem ceva…
Am un prieten bun Horia, care imi trimite 180 ron card cadou (cupon). Si il folosesc pentru a cumpara produsele de mai sus.
Atunci pe “Orders” voi avea si discount 180. Care se reflecta si pe order_items → item 1 va avea price 50, discount 20, final_price 30; item 2 va avea price 100, qty 4, discount 160, final_price 240.
Astfel eu pot emite facturi separat 30 si respectiv 240, pot avea primul produs returnat si al doilea livrat… Comanda in status finalizat, cu produse in status returnat si livrat.
Deci mie mi se pare mai logic un foreign key intre items si orders prin order_id, decat ProductId. Iar item va avea un product_id, dar fara ON DELETE,
Da, nu as fi chiar de acord. Mai bine le pui intr-o tabela / baza de date separata. Cum dadeai tu exemplu cu eMag, oare ei mai tin in “Products” nokia 3310, ca un “update Products… where” sa caute in sute de mii de row-uri?
Cred ca am trecut toti de faptul ca nimic nu este cu adevarat sters de pe net.
Imi imaginez ca Amazon nu foloseste MySQL cu foreign keys si constraints…
Înțeleg ce zice @horia141 referitor la consistență, dar eu prefer să nu folosesc foreign keys din același motiv pentru care nu folosesc stored procedures: Consider că orice modificare a datelor stocate în baza de date ar trebui să fie făcută explicit de către aplicație. Singurul lucru care accept să fie controlat de baza de date este cheia primară, care nu are legătură cu logica aplicației.
Nu zic că așa-i mai bine ci doar că asta prefer eu (control freak) 
Cred ca e ceva context ce-mi lipseste din thread-ul asta.
Asa ca am cautat pe net, sa vad daca e totusi ceva comun asta. Thread-ul urmator de pe SO mi s-a parut relevant.
Primul raspuns mi s-a parut bine pus la punct, cu pros si contras:
Reasons to use Foreign Keys:
- you won’t get Orphaned Rows
- you can get nice “on delete cascade” behavior, automatically cleaning up tables
- knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality.
FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance- they enable all kinds of auto-generated support – ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc
- someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented
Reasons not to use Foreign Keys:
- you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn
by enforcing relationships- FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want.
Dar tot thread-ul merita IMO.
Sute de mii de row-uri e small data in zilele noastre. Daca whare-ul e pe un index n-ar trebui sa ai probleme. Si chiar daca nu e, daca nu faci foarte des operatia asta o sa fi OK.
Trebuie sa ai grija sa filtrezi ce e activ de ce nu e activ, care e o complexitate in plus. Dar de exemplu, query-uri de genul “care-i cel mai popular produs din toate timpurile?” devin mai usor de facut. E un trade-off intre unde vrei sa ai complexitatea, dar nu scapi de ea doar ca muti datele intr-o alta tabela.
Delete-ul in sine poate sa fie mai complicat decat doar mutarea in starea “archived”. Poti sa blankezi campurile sensitive de exemplu.
In multe produse nici nu ai voie sa stergi ceva fizic. De exemplu, daca esti un SaaS email provider pentru o companie, cand cineva sterge un mesaj nu ai voie in multe jurisdictii chiar sa-i dai delete. Pentru ca email-ul ala poate fi folosit dup’aia intr-o investigatie a politiei etc. Asa ca trebuie doar marcat ca sters. Ditto pentru multe companii mari pentru cand le bate FBI sau NSA la usa. Nu e asa de alb si negru ca “niste companii rele nu vor sa ne stearga informatia din bazele lor de date”.
Cel putin in versiunea originala a lui Dynamo, foloseau MySql si BerkelyDB ca strat de stocare. IDK cum s-au schimbat lucrurile, ca n-au mai zis public, dar imi imaginez ca inca sunt bazele de date relationale pe undeva. Nu toate datele sunt mari si pentru unele chestii chiar ai nevoie de ce-ti ofera un RDBMS - acid, transactii etc. Ma gandesc la date despre companiile din marketplace, furnizori etc.