B_gd_n[ ]Sahlean:Nu înţeleg de ce ai în CHITANŢĂ cheia externă ID_CONTRACT ?
Trebuie să spun cine a plătit acea sumă. Dacă nu aş fi avut ID_CONTRACT, aş fi avut ID_CLIENT, însă am impresia că atunci când se achită o sumă, se precizează exact la ce contract se referă (nu doar clientul), aşa că am preferat să ţin doar ID_CONTRACT, iar ID_CLIENT se poate regăsi din tabela CONTRACTE.
B_gd_n[ ]Sahlean: Deci ID_CONTRACT este cheie externă şi în RATĂ, şi în PLATA_RATA şi în CHITANŢĂ ?!
Prin această cheie externă, asigur regula de business care spune că o chitanţă poate plăti doar ratele aferente acelui contract. Motivul care m-a făcut să aleg această soluţie a fost faptul că am avut impresia că primary key-ul din tabela RATE este compus din ID_CONTRACT+NR_RATA, deci dacă vroiam să fac un foreign key în tabela PLATA_RATA către tabela RATE, oricum trebuia să includ coloana ID_CONTRACT şi dacă tot o am şi în tabela CHITANTE, atunci pun şi foreign key-ul (care era comentat) ca să mai acopăr o regulă de business.
Oricum, dacă n-ar fi fost ID_CONTRACT, ar fi fost ID_Client, şi tot ar fi trebuit să asigur regula că o chitanţă poate plăti doar facturile aceluiaşi client. De exemplu, în cazul clasic, avem:
CREATE TABLE Facturi (
ID_Factura int IDENTITY PRIMARY KEY,
ID_Client int NOT NULL REFERENCES Clienti,
DataFactura smalldatetime NOT NULL,
NumarFactura varchar(10) NOT NULL UNIQUE,
TotalFactura money NOT NULL
)
--CREATE TABLE DetaliiFacturi ...
CREATE TABLE Chitante (
ID_Chitanta int IDENTITY PRIMARY KEY,
ID_Client int NOT NULL REFERENCES Clienti,
DataChitanta smalldatetime NOT NULL,
NumarChitanta varchar(10) NOT NULL UNIQUE,
SumaIncasata money NOT NULL
)
CREATE TABLE Asocieri (
ID_Factura int REFERENCES Facturi,
ID_Chitanta int REFERENCES Chitante,
ValoareAsociere money NOT NULL,
PRIMARY KEY (ID_Factura, ID_Chitanta)
)
În acest caz, trebuie să verific prin trigger-e că asocierile se fac doar între facturi şi chitanţe care aparţin aceluiaşi client. Dacă adaug şi coloana ID_Client în tabela Asocieri, atunci această verificare poate fi făcută declarativ, nu procedural (porţiunile adăugate sunt marcate apăsat):
CREATE TABLE Facturi (
ID_Factura int IDENTITY PRIMARY KEY,
ID_Client int NOT NULL REFERENCES Clienti,
DataFactura smalldatetime NOT NULL,
NumarFactura varchar(10) NOT NULL UNIQUE,
TotalFactura money NOT NULL,
UNIQUE (ID_Client, ID_Factura)
)
--CREATE TABLE DetaliiFacturi ...
CREATE TABLE Chitante (
ID_Chitanta int IDENTITY PRIMARY KEY,
ID_Client int NOT NULL REFERENCES Clienti,
DataChitanta smalldatetime NOT NULL,
NumarChitanta varchar(10) NOT NULL UNIQUE,
SumaIncasata money NOT NULL
UNIQUE (ID_Client, ID_Chitanta)
)
CREATE TABLE Asocieri (
ID_Factura int REFERENCES Facturi,
ID_Chitanta int REFERENCES Chitante,
ID_Client int REFERENCES Clienti,
ValoareAsociere money NOT NULL,
PRIMARY KEY (ID_Factura, ID_Chitanta),
FOREIGN KEY (ID_Client, ID_Factura) REFERENCES Facturi (ID_Client, ID_Factura),
FOREIGN KEY (ID_Client, ID_Chitanta) REFERENCES Facturi (ID_Client, ID_Chitanta)
)
E adevărat, e vorba de o denormalizare a tabelei Asocieri: coloana ID_Client are o dependenţă funcţională faţă de ID_Factura (sau faţă de ID_Chitanta), care nu este cheia primară (ci doar o parte din aceasta). Are şi dezavantaje: ce facem dacă se modifică clientul unei chitanţe ? În oricare din cele două variante, trebuie să ştergem toate asocierile (sau să interzicem modificarea), pentru că nu pot rămâne asocieri cu facturi ale altui client. Dar în această variantă, nu putem să facem ştergerea printr-un trigger normal, pentru că foreign key-ul se verifică înainte de trigger-ele AFTER, deci trebuie să facem un trigger INSTEAD OF, ceea ce e un pic mai neplăcut. Are şi alte avantaje (în afară de eliminarea trigger-ului menţionat): dacă te interesează toate asocierile pentru un anumit client, poţi să iei direct datele din tabela Asocieri, nu mai trebuie să faci join cu Facturi sau cu Chitante. Deci e discutabil care dintre cele două variante e preferabilă...
B_gd_n[ ]Sahlean: Nu înţeleg de ce ai definit cheia externă ID_CONTRACT (ID_CONTRACT INT NOT NULL REFERENCES CONTRACTE) din moment ce ai definit şi cheia externă ID_CONTRACT + NR_RATA (FOREIGN KEY (ID_CONTRACT, NR_RATA) REFERENCES RATE (ID_CONTRACT, NR_RATA)
) ?
Din punctul de vedere al asigurării integrităţii, într-adevăr această cheie este redundantă. Totuşi, am adăugat-o din două motive:
1. Documentarea bazei de date (atunci când vreau să găsesc toate coloanele care se referă la contracte, pot să fiu sigur că le găsesc pe toate).
2. Poate fi util pentru query optimizer, care ar putea să se bazeze pe acest foreign key ca să mai optimizeze unele query-uri (vezi aici).
B_gd_n[ ]Sahlean:
Şi în cazul acesta (tabela PLATI_RATE propusă de tine) cum este rezolvată cerinţa: "Clientul poate sa achite in avans una sau mai multe rate, sau poate sa plateasca o suma mai mare decit o rata dar mai mica decit 2 rate" ? Diferenţa dintre suma paltita şi suma ratei ?
Păi, simplu... (sau poate nu înţeleg întrebarea): de exemplu, dacă avem chitanţa nr. 111 pentru suma de 230 lei şi ratele nr.1, nr.2, nr.3 şi nr.4 fiecare de câte 100 lei, atunci în tabela CHITANTE avem (simplific, pentru claritate):
Nr_Chitanta Suma_Incasata
111 230
iar în tabela PLATI_RATE avem (de asemenea, simplificat):
Nr_Chitanta Nr_Rata Valoare_Asociere
111 1 100
111 2 100
111 3 30
Iar apoi dacă avem chitanţa nr. 222 pentru suma de 170 lei, atunci mai adăugăm:
Nr_Chitanta Nr_Rata Valoare_Asociere
222 3 70
222 4 100
Ar trebui să mai avem ceva care ne asigură că SUM(Valoare_Asociere) GROUP BY Nr_Chitanta este egal cu Suma_Incasata, dar acest lucru nu se poate face prin trigger-e, deci va trebui verificat la sfârşit (după ce sunt adăugate toate datele).
Răzvan