Este vreodată în regulă să folosiți liste într-o bază de date relațională? (Inginerie software, Bază De Date, Proiectarea Bazei De Date, Sql, Bază De Date Relațională)

linus72982 a intrebat.

Am încercat să proiectez o bază de date pentru a merge cu un concept de proiect și am dat peste ceea ce pare a fi o problemă intens dezbătută. Am citit câteva articole și câteva răspunsuri de pe Stack Overflow care afirmă că nu este niciodată (sau aproape niciodată) în regulă să stocați o listă de ID-uri sau altele asemenea într-un câmp – toate datele ar trebui să fie relaționale, etc.

Problema cu care mă confrunt, totuși, este că încerc să fac un task assigner. Oamenii vor crea sarcini, le vor atribui la mai multe persoane și se va salva în baza de date.

Desigur, dacă voi salva aceste sarcini individual în „Persoană”, va trebui să am zeci de coloane fictive „TaskID” și să le microgestionez, deoarece pot exista între 0 și 100 de sarcini atribuite unei persoane, să zicem.

Apoi, din nou, dacă salvez sarcinile într-un tabel „Tasks”, va trebui să am zeci de coloane fictive „PersonID” și să le microgestionez – aceeași problemă ca și înainte.

Pentru o problemă ca aceasta, este în regulă să salvez o listă de ID-uri luând o formă sau alta sau nu mă gândesc la o altă modalitate prin care acest lucru este realizabil fără a încălca principiile?

Comentarii

    22

  • Îmi dau seama că acest lucru este etichetat „bază de date relațională”, așa că îl voi lăsa ca un comentariu și nu ca un răspuns, dar în alte tipuri de baze de date se face are sens să se stocheze liste. Cassandra îmi vine în minte, deoarece nu are îmbinări. –  > Por Căpitanul Man.
  • Bună treabă în a cerceta și apoi a întreba aici! Într-adevăr, „recomandarea” de a nu încălca niciodată prima formă normală v-a prins foarte bine, pentru că într-adevăr ar trebui să veniți cu o altă abordare, relațională, și anume o relație „mulți-la-mulți”, pentru care există un model standard în bazele de date relaționale care ar trebui folosit. –  > Por JimmyB.
  • „Este vreodată în regulă” da…. orice ar urma, răspunsul este da. Atâta timp cât aveți un motiv valabil. Întotdeauna există un caz de utilizare care te obligă să încalci cele mai bune practici pentru că are sens să faci acest lucru. (În cazul tău, însă, cu siguranță nu ar trebui să o faci) -.  > Por xyious.
  • În prezent, folosesc o matrice (nu un șir de caractere delimitat – un VARCHAR ARRAY) pentru a stoca o listă de etichete. Probabil că nu așa vor ajunge să fie stocate mai târziu, dar listele pot fi extrem de utile în timpul etapelor de prototipare, când nu ai nimic altceva spre care să îndrepți și nu vrei să construiești întreaga schemă a bazei de date înainte de a putea face orice altceva. –  > Por Finanțați procesul Monicăi.
  • @Ben „(deși nu vor fi indexabile)” — în Postgres, mai multe interogări împotriva coloanelor JSON (și probabil XML, deși nu am verificat) sunt indexabile. –  > Por Finanțați procesul Monicăi.
9 răspunsuri
whatsisname

Cuvântul cheie și conceptul cheie pe care trebuie să le investighezi este bază de date normalizare.

Mai degrabă decât să adăugați informații despre misiuni în tabelele de persoane sau de sarcini, adăugați un tabel nou cu aceste informații despre misiuni, cu relații relevante.

De exemplu, aveți următoarele tabele:

Persoane:

+−−−−+−−−−−−−−−−−+
| ID |    Name   |
+====+===========+
|  1 |  Alfred   |
|  2 |  Jebediah |
|  3 |  Jacob    |
|  4 |  Ezekiel  |
+−−−−+−−−−−−−−−−−+

Sarcini:

+−−−−+−−−−−−−−−−−−−−−−−−−−+
| ID |        Name        |
+====+====================+
|  1 |  Feed the Chickens |
|  2 |  Plow              |
|  3 |  Milking Cows      |
|  4 |  Raise a barn      |
+−−−−+−−−−−−−−−−−−−−−−−−−−+

Apoi ați crea un al treilea tabel cu „Atribuții”. Acest tabel ar modela relația dintre persoane și sarcini:

+−−−−+−−−−−−−−−−−+−−−−−−−−−+
| ID |  PersonId |  TaskId |
+====+===========+=========+
|  1 |         1 |       3 |
|  2 |         3 |       2 |
|  3 |         2 |       1 |
|  4 |         1 |       4 |
+−−−−+−−−−−−−−−−−+−−−−−−−−−+

Am avea apoi o constrângere Foreign Key, astfel încât baza de date să impună faptul că PersonId și TaskIds trebuie să fie ID-uri valide pentru aceste elemente străine. Pentru primul rând, putem vedea PersonId is 1, astfel Alfred, este atribuit lui TaskId 3, Vaci de muls.

Ceea ce ar trebui să puteți vedea aici este că puteți avea oricât de puține sau oricât de multe atribuții pe sarcină sau pe persoană doriți. În acest exemplu, Ezekiel nu primește nicio sarcină, iar Alfred este atribuit 2. Dacă aveți o sarcină cu 100 de persoane, făcând SELECT PersonId from Assignments WHERE TaskId=<whatever>; va produce 100 de rânduri, cu o varietate de persoane diferite alocate. Puteți WHERE pe PersonId pentru a găsi toate sarcinile atribuite acelei persoane.

Dacă doriți să obțineți interogări care să înlocuiască ID-urile cu numele și sarcinile, atunci trebuie să învățați cum să faceți JOIN tables.

Comentarii

    88

  • Cuvântul cheie pe care doriți să îl căutați pentru a afla mai multe este „relație mulți-la-mulți” –  > Por BlueRaja – Danny Pflughoeft.
  • 35

  • Pentru a dezvolta puțin comentariul lui Thierrys: S-ar putea să credeți că nu este nevoie să normalizați pentru că am nevoie doar de X și este foarte simplu să stocați lista de ID-uri, dar pentru orice sistem care poate fi extins mai târziu veți regreta că nu l-ați normalizat mai devreme. Normalizați întotdeauna; singura întrebare este la ce formă normală –  > Por Jan Doggen.
  • De acord cu @Jan – împotriva judecății mele mai bune, am permis echipei mele să ia o scurtătură de proiectare cu ceva timp în urmă, stocând JSON în schimb pentru ceva care „nu va trebui să fie extins”. Asta a durat cam șase luni FML. Upgrade-ul nostru a avut apoi o luptă urâtă pe mâini pentru a migra JSON la schema cu care ar fi trebuit să începem. Chiar ar fi trebuit să știu mai bine. –  > Por Curse de ușurință pe orbită.
  • @Deduplicator: este doar o reprezentare a unei coloane cheie primară de tip „garden-variety”, cu auto-increment de numere întregi. Chestii destul de tipice. –  > Por whatsisname.
  • @whatsisname În ceea ce privește tabelul Persoane sau Sarcini, sunt de acord cu tine. Pe o tabelă punte în care singurul scop este de a reprezenta relația mulți-la-mulți între două alte tabele care au deja chei surogat? Eu nu aș adăuga una fără un motiv întemeiat. Este doar un cost suplimentar, deoarece nu va fi niciodată utilizată în interogări sau relații. –  > Por jpmc26.
GrandmasterB

Pui două întrebări aici.

În primul rând, întrebați dacă este în regulă să stocați listele serializate într-o coloană. Da, este în regulă. Dacă proiectul dvs. solicită acest lucru. Un exemplu ar putea fi ingredientele unui produs pentru o pagină de catalog, unde nu aveți dorința de a încerca să urmăriți fiecare ingredient în parte.

Din păcate, a doua întrebare descrie un scenariu în care ar trebui să optați pentru o abordare mai relațională. Veți avea nevoie de 3 tabele. Una pentru persoane, una pentru sarcini și una care să mențină lista cu ce sarcină este atribuită fiecărei persoane. Ultima tabelă ar fi verticală, un rând pentru fiecare combinație persoană/activitate, cu coloane pentru cheia primară, ID-ul activității și ID-ul persoanei.

Comentarii

  • Exemplul de ingredient la care faceți referire este corect la suprafață; dar în acest caz ar fi vorba de text simplu. Nu este o listă în sensul de programare (cu excepția cazului în care vă referiți la faptul că șirul este o listă de caractere, ceea ce, evident, nu este cazul). Faptul că OP își descrie datele ca fiind „o listă de ID-uri” (sau chiar doar „o listă de […]”) implică faptul că, la un moment dat, manipulează aceste date ca obiecte individuale. –  > Por Flater.
  • @Flater: Dar este o listă. Trebuie să puteți să o reformatați ca (în mod diferit) o listă HTML, o listă Markdown, o listă JSON etc. pentru a vă asigura că elementele sunt afișate corect în (în mod diferit) o pagină web, un document de text simplu, o aplicație mobilă… și nu prea puteți face asta cu textul simplu. –  > Por Kevin.
  • @Kevin Dacă acesta este obiectivul tău, atunci este mult mai ușor și mai ușor de realizat prin stocarea ingredientelor într-un tabel! Ca să nu mai vorbim de faptul că, mai târziu, oamenii ar … oh, nu știu, să zicem, să își dorească înlocuitori recomandați, sau ceva prostesc, cum ar fi să caute toate rețetele fără nici alune, sau gluten, sau proteine animale… –  > Por Dan Bron.
  • @DanBron: YAGNI. În acest moment folosim doar o listă pentru că ușurează logica UI. Dacă avem nevoie sau vom avea nevoie de un comportament de tip listă în stratul de logică de afaceri, atunci ar trebui să fie normalizat într-un tabel separat. Tabelele și îmbinările nu sunt neapărat costisitoare, dar nu sunt gratuite, și aduc întrebări despre ordinea elementelor („Ne interesează ordinea ingredientelor?”) și normalizarea ulterioară („Vei transforma „3 ouă” în („ouă”, 3)? Dar ‘Sare, după gust’, este (‘sare’, NULL)?”). –  > Por Kevin.
  • @Kevin: YAGNI greșește destul de mult aici. Tu însuți ai argumentat necesitatea de a putea transforma lista în mai multe moduri (HTML, markdown, JSON) și, prin urmare, susții că aveți nevoie de elementele individuale ale listei. Cu excepția cazului în care aplicațiile de stocare a datelor și de „manipulare a listei” sunt două aplicații dezvoltate independent (și rețineți că straturi de aplicații separate != aplicații separate), structura bazei de date ar trebui să fie întotdeauna creată pentru a stoca datele într-un format care să le lase ușor disponibile – evitând în același timp logica suplimentară de analiză/conversie. –  > Por Flater.
Mike Partridge

Ceea ce descrieți este cunoscut ca o relație „mulți la mulți”, în cazul dvs. între Person și Task. De obicei, este implementată folosind un al treilea tabel, numit uneori tabel de „legătură” sau tabel de „referință încrucișată”. De exemplu:

create table person (
    person_id integer primary key,
    ...
);

create table task (
    task_id integer primary key,
    ...
);

create table person_task_xref (
    person_id integer not null,
    task_id integer not null,
    primary key (person_id, task_id),
    foreign key (person_id) references person (person_id),
    foreign key (task_id) references task (task_id)
);

Comentarii

  • Este posibil să doriți, de asemenea, să adăugați un index cu task_id mai întâi, dacă este posibil să efectuați interogări filtrate în funcție de sarcină. –  > Por jpmc26.
  • Cunoscută și sub numele de tabel punte. De asemenea, mi-aș dori să vă pot da un plus pentru că nu aveți o coloană de identitate, deși aș recomanda un index pe fiecare coloană. –  > Por jmoreno.
Phill W.

… nu este niciodată (sau aproape niciodată) în regulă să stocați o listă de ID-uri sau altele asemenea într-un câmp

Singurul moment în care s-ar putea stoca mai mult de un element de date într-un singur câmp este atunci când acel câmp este doar folosit vreodată ca o singură entitate și este niciodată considerat ca fiind alcătuit din aceste elemente mai mici. Un exemplu ar putea fi o imagine, stocată într-un câmp BLOB. Aceasta este formată din foarte multe elemente mai mici (octeți), dar acestea înseamnă că nimic pentru baza de date și pot fi utilizate doar împreună (și arată frumos pentru un utilizator final).

Deoarece o „listă” este, prin definiție, alcătuită din elemente mai mici (elemente), nu este cazul aici și ar trebui să normalizați datele.

… dacă salvez aceste sarcini în mod individual în „Person”, va trebui să am zeci de coloane fictive „TaskID” …

Nu, veți avea câteva rânduri într-o tabelă de intersecție (cunoscută și ca entitate slabă) între Person și Task. Bazele de date se pricep foarte bine la lucrul cu multe rânduri; de fapt, sunt destul de proaste la lucrul cu multe coloane [repetate].

Exemplu frumos și clar dat de whatsisname.

Comentarii

  • Atunci când se creează sisteme din viața reală, „niciodată să nu spui niciodată” este o regulă foarte bună de urmat. –  > Por l0b0.
  • În multe cazuri, costul per element al menținerii sau recuperării unei liste în formă normalizată poate depăși cu mult costul menținerii elementelor sub formă de blob, deoarece fiecare element al listei ar trebui să dețină identitatea elementului principal cu care este asociat și locația sa în cadrul listei, pe lângă datele efective. Chiar și în cazurile în care codul ar putea beneficia de posibilitatea de a actualiza unele elemente ale listei fără a actualiza întreaga listă, ar putea fi mai ieftin să se stocheze totul sub formă de blob și să se rescrie totul ori de câte ori trebuie rescris ceva. –  > Por supercat.
miroxlav

Poate fi legitim în anumite câmpuri precalculate.

Dacă unele dintre interogări sunt costisitoare și decideți să mergeți pe câmpuri precalculate și actualizate automat cu ajutorul declanșatorilor bazei de date, atunci ar putea fi legitim să păstrați listele în interiorul unei coloane.

De exemplu, în interfața de utilizare doriți să afișați această listă folosind vizualizarea grilă, unde fiecare rând poate deschide detaliile complete (cu listele complete) după ce faceți dublu clic:

REGISTERED USER LIST
+------------------+----------------------------------------------------+
|Name              |Top 3 most visited tags                             |
+==================+====================================================+
|Peter             |Design, Fitness, Gifts                              |
+------------------+----------------------------------------------------+
|Lucy              |Fashion, Gifts, Lifestyle                           |
+------------------+----------------------------------------------------+

Păstrați a doua coloană actualizată prin declanșator atunci când clientul vizitează un articol nou sau prin sarcină programată.

Puteți face ca un astfel de câmp să fie disponibil chiar și pentru căutare (ca text normal).

Pentru astfel de cazuri, păstrarea listelor este legitimă. Trebuie doar să luați în considerare cazul în care este posibil să depășiți lungimea maximă a câmpului.


De asemenea, dacă utilizați Microsoft Access, a oferit câmpuri cu mai multe valențe reprezintă un alt caz special de utilizare. Acestea gestionează automat listele dvs. într-un câmp.

Dar puteți oricând să reveniți la forma standard normalizată prezentată în alte răspunsuri.


Rezumat: Formele normale ale bazei de date sunt teoretice model necesar pentru înțelegerea unor aspecte importante ale modelării datelor. Dar, desigur, normalizarea nu ia în considerare performanța sau alte costuri de recuperare a datelor. Aceasta nu intră în sfera de aplicare a acestui model teoretic. Dar stocarea listelor sau a altor duplicate precalculate (și controlate) este adesea necesară în cadrul implementării practice.

Având în vedere cele de mai sus, în implementarea practică, am prefera o interogare care se bazează pe o formă normală perfectă și care durează 20 de secunde sau o interogare echivalentă care se bazează pe valori precalculate și care durează 0,08 s? Nimănui nu-i place ca produsul său software să fie acuzat de lentoare.

Comentarii

  • Acesta poate fi legitim chiar și fără valori precalculate. Am făcut-o de câteva ori în cazul în care datele sunt stocate în mod corespunzător, dar din motive de performanță este utilă introducerea câtorva rezultate din memoria cache în înregistrările principale. –  > Por Loren Pechtel.
  • @LorenPechtel – Da, mulțumesc, în utilizarea mea a termenului precalculată includ și cazurile de valori stocate în memoria cache acolo unde este necesar. În sistemele cu dependențe complexe, acestea sunt modalitatea de a menține performanța normală. Iar dacă sunt programate cu un know-how adecvat, aceste valori sunt fiabile și mereu sincronizate. Pur și simplu nu am vrut să adaug cazuri de cache în răspuns pentru a păstra răspunsul simplu și în siguranță. Oricum a fost votat în jos. 🙂 –  > Por miroxlav.
  • @LorenPechtel De fapt, acesta ar fi totuși un motiv prost… datele din cache ar trebui păstrate într-o memorie cache intermediară, iar cât timp cache-ul este încă valabil, interogarea respectivă nu ar trebui să ajungă niciodată în baza de date principală. –  > Por Tezra.
  • @Tezra Nu, eu spun că, uneori, o bucată de date dintr-o tabelă secundară este necesară suficient de des pentru a face să aibă sens să se pună o copie în înregistrarea principală. (Exemplul pe care l-am făcut – tabelul angajaților include ultima oră de intrare și ultima oră de ieșire. Acestea sunt folosite doar pentru afișare, orice calcul real provine din tabelul cu înregistrările de intrare/ieșire). –  > Por Loren Pechtel.
Mad Myche

Având în vedere două tabele; le vom numi Person și Task, fiecare cu propriul ID (PersonID, TaskID)… ideea de bază este de a crea un al treilea tabel pentru a le lega împreună. Vom numi acest tabel PersonToTask. Acesta ar trebui să aibă cel puțin propriul ID, precum și celelalte două, astfel încât, atunci când va trebui să atribuiți o sarcină cuiva, nu va mai fi nevoie să actualizați tabelul Person, ci doar să inserați o nouă linie în PersonToTaskTable.Și întreținerea devine mai ușoară – dacă este nevoie să ștergeți o sarcină, aceasta devine doar un DELETE bazat pe TaskID, fără a mai actualiza tabelul Person și analizele asociate.

CREATE TABLE dbo.PersonToTask (
    pttID INT IDENTITY(1,1) NOT NULL,
    PersonID INT NULL,
    TaskID   INT NULL
)

CREATE PROCEDURE dbo.Task_Assigned (@PersonID INT, @TaskID INT)
AS
BEGIN
    INSERT PersonToTask (PersonID, TaskID)
    VALUES (@PersonID, @TaskID)
END

CREATE PROCEDURE dbo.Task_Deleted (@TaskID INT)
AS
BEGIN
    DELETE PersonToTask  WHERE TaskID = @TaskID
    DELETE Task          WHERE TaskID = @TaskID
END

Ce ziceți de un raport simplu sau cine sunt toți cei care au fost atribuiți unei sarcini?

CREATE PROCEDURE dbo.Task_CurrentAssigned (@TaskID INT)
AS
BEGIN
    SELECT PersonName
    FROM   dbo.Person
    WHERE  PersonID IN (SELECT PersonID FROM dbo.PersonToTask WHERE TaskID = @TaskID)
END

Desigur, puteți face mult mai mult; un raport de timp ar putea fi realizat dacă ați adăuga câmpuri de tip DateTime pentru TaskAssigned și TaskCompleted. Totul depinde de dvs.

Double E CPU

Ar putea funcționa dacă, să zicem, aveți chei primare lizibile de către om și doriți o listă de numere de sarcini fără a fi nevoit să vă ocupați de natura verticală a structurii unui tabel, adică un prim tabel mult mai ușor de citit.

------------------------  
Employee Name | Task 
Jack          |  1,2,5
Jill          |  4,6,7
------------------------

------------------------  
Employee Name | Task 
Jack          |  1
Jack          |  2
Jack          |  5
Jill          |  4
Jill          |  6
Jill          |  7
------------------------

Întrebarea ar fi atunci: lista de sarcini ar trebui să fie stocată sau generată la cerere, ceea ce ar depinde în mare măsură de cerințe precum: cât de des este nevoie de listă, cât de precise sunt rândurile de date existente, cum vor fi utilizate datele etc… după care ar trebui analizate compromisurile cu experiența utilizatorului și îndeplinirea cerințelor.

De exemplu, comparând timpul necesar pentru a reaminti cele 2 rânduri cu timpul necesar pentru a executa o interogare care ar genera cele 2 rânduri. Dacă durează mult timp și utilizatorul nu are nevoie de cea mai recentă listă (*se așteaptă la mai puțin de o modificare pe zi), atunci ar putea fi stocată.

Sau, dacă utilizatorul are nevoie de o înregistrare istorică a sarcinilor care i-au fost atribuite, ar fi de asemenea logic ca lista să fie stocată. Așadar, totul depinde de ceea ce faceți, dar niciodată să nu spuneți niciodată.

Comentarii

  • După cum ați spus, totul depinde de modul în care vor fi recuperate datele. Dacă interoghezi acest tabel numai după numele utilizatorului, atunci câmpul „list” este perfect adecvat. Cu toate acestea, cum puteți interoga un astfel de tabel pentru a afla cine lucrează la sarcina nr. 1234567 și să păstrați performanța? Aproape orice fel de funcție String „find-X-anywhere-in-the-field” va face ca o astfel de interogare să facă o scanare a tabelului, încetinind lucrurile până la o viteză de lucru foarte mică. Cu date corect normalizate și indexate corespunzător, acest lucru nu se întâmplă. –  > Por Phill W..
Bloke Down The Pub

Iei ceea ce ar trebui să fie un alt tabel, îl întorci la 90 de grade și îl bagi într-un alt tabel.

Este ca și cum ai avea un tabel de comenzi în care ai itemProdcode1, itemQuantity1, itemPrice1 … itemProdcode37, itemQuantity37, itemPrice37. În afară de faptul că este dificil de gestionat programatic, puteți garanta că mâine cineva va dori să comande 38 de lucruri.

Eu aș proceda în felul dvs. doar dacă „lista” nu este cu adevărat o listă, adică dacă este un întreg și fiecare articol individual nu se referă la o entitate clară și independentă. În acest caz, puneți totul într-un tip de date care este suficient de mare.

Astfel, o comandă este o listă, o listă de materiale este o listă (sau o listă de liste, ceea ce ar fi un coșmar și mai mare dacă ar fi implementată „lateral”). Dar o notă/comentariu și o poezie nu sunt.

NoBugs

Dacă „nu e ok”, atunci e destul de rău că fiecare site WordPress are vreodată o listă în wp_usermeta cu wp_capabilities pe un rând, lista dismissed_wp_pointers pe un rând și altele…

De fapt, în cazuri ca acesta s-ar putea să fi mai bine pentru viteză, deoarece veți dori aproape întotdeauna să lista. Dar WordPress nu este cunoscut ca fiind un exemplu perfect de bune practici.