Va sempre bene usare le liste in un database relazionale?

La parola chiave e il concetto chiave da indagare è database normalizzazione .

Ciò che faresti, piuttosto che aggiungere informazioni sui compiti alle tabelle delle persone o delle attività, è aggiungere una nuova tabella con le informazioni sul compito, con le relazioni pertinenti.

Esempio, hai le seguenti tabelle:

Persone:

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

Compiti:

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

Creerai quindi una terza tabella con le assegnazioni. Questa tabella modellerebbe la relazione tra le persone e le attività:

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

Avremmo quindi un vincolo di chiave esterna, in modo che il database applichi che PersonId e TaskId devono essere ID validi per quegli elementi estranei. Per la prima riga, possiamo vedere PersonId is 1 , quindi Alfred , è assegnato a TaskId 3 , Mungitura delle vacche .

Quello che dovresti essere in grado di vedere qui è che potresti avere come pochi o tanti incarichi per compito o per persona come vuoi. In questo esempio, Ezekiel non viene assegnato alcun compito e Alfred è assegnato 2. Se hai un compito con 100 persone, facendo SELECT PersonId from Assignments WHERE TaskId=<whatever>; otterrai 100 righe, con una varietà di diverse persone assegnate. Puoi WHERE sul PersonId per trovare tutti i compiti assegnati a quella persona.

Se vuoi restituire le query sostituendo gli ID con i nomi e le attività, allora imparerai come unire le tabelle.

Stai facendo due domande qui.

Per prima cosa, chiedi se è ok per archiviare gli elenchi serializzati in una colonna. Sì, è tutto a posto. Se il tuo progetto lo richiede. Un esempio potrebbe essere gli ingredienti del prodotto per una pagina del catalogo, in cui non desideri provare a tracciare ciascun ingrediente individualmente.

Sfortunatamente la tua seconda domanda descrive uno scenario in cui dovresti optare per un approccio più relazionale. Avrai bisogno di 3 tavoli. Uno per le persone, uno per i compiti e uno che mantiene l'elenco di quale compito è assegnato a quali persone. Quest'ultima sarà una combinazione verticale, una riga per persona / compito, con le colonne per la chiave primaria, l'ID dell'attività e l'ID della persona.

Quello che stai descrivendo è noto come una relazione "molti a molti", nel tuo caso tra Person e Task . In genere viene implementato utilizzando una terza tabella, a volte denominata tabella "link" o "cross-reference". Ad esempio:

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)
);

... it's never (or almost never) okay to store a list of IDs or the like in a field

L'unica volta che potresti memorizzare più di un elemento di dati in un singolo campo è quando quel campo è solo mai usato come entità singola ed è < em> mai considerato come composto da quegli elementi più piccoli. Un esempio potrebbe essere un'immagine, memorizzata in un campo BLOB. È composto da un sacco di elementi più piccoli (byte), ma questi significano niente nel database e possono essere usati tutti insieme (e sembrano belli per un utente finale).

Poiché una "lista" è, per definizione, costituita da elementi più piccoli (elementi), non è questo il caso qui e dovresti normalizzare i dati.

... if I save these tasks individually in "Person", I'll have to have dozens of dummy "TaskID" columns ...

No. Avrai poche righe in una tabella di intersezione (a.k.a entità debole) tra persona e attività. I database sono veramente bravi a lavorare con molte righe; in realtà sono piuttosto sciatti a lavorare con molte [ripetute] colonne.

Bel chiaro esempio dato da whatsisname.

Potrebbe essere legittimo in alcuni campi precalcolati.

Se alcune delle tue query sono costose e decidi di andare con i campi precalcolati aggiornati automaticamente utilizzando i trigger di database, allora potrebbe essere legittimo mantenere gli elenchi all'interno di una colonna.

Ad esempio, nell'interfaccia utente desideri mostrare questo elenco utilizzando la visualizzazione griglia, in cui ogni riga può aprire i dettagli completi (con elenchi completi) dopo aver fatto doppio clic:

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

La seconda colonna viene aggiornata per trigger quando il client visita un nuovo articolo o un'attività pianificata.

Puoi rendere questo campo disponibile anche per la ricerca (come testo normale).

Per tali casi, mantenere le liste è legittimo. Devi solo considerare il caso di possibile superamento della lunghezza massima del campo.

Inoltre, se utilizzi Microsoft Access, offri campi multivalore sono un altro caso di utilizzo speciale. Gestiscono automaticamente le tue liste in un campo.

Ma puoi sempre tornare al normale formato normalizzato mostrato in altre risposte.

Riepilogo: le normali forme di database sono modello teorico richiesto per comprendere aspetti importanti della modellazione dei dati. Ma ovviamente la normalizzazione non tiene conto delle prestazioni o di altri costi di recupero dei dati. È al di fuori di questo modello teorico. Tuttavia, l'implementazione pratica richiede spesso l'archiviazione di elenchi o altri duplicati precalcolati (e controllati).

Alla luce di quanto sopra, nell'implementazione pratica, preferiremmo la query basandosi su una forma normale perfetta ed eseguendo 20 secondi o query equivalenti facendo affidamento su valori precalcolati che impiegano 0,08 s? A nessuno piace che il loro prodotto software sia accusato di lentezza.

Stai prendendo quello che dovrebbe essere un altro tavolo, girandolo di 90 gradi e inseriscilo in un altro tavolo.

È come avere una tabella degli ordini in cui hai itemProdcode1, itemQuantity1, itemPrice1 ... itemProdcode37, itemQuantity37, itemPrice37. Oltre ad essere scomodo da gestire a livello di programmazione, puoi garantire che domani qualcuno vorrà ordinare 38 cose.

Lo farei a modo tuo solo se la 'lista' non è realmente una lista, vale a dire dove si trova nel suo complesso e ogni singolo elemento pubblicitario non si riferisce ad alcuna entità chiara e indipendente. In tal caso, basta inserire tutto in un tipo di dati abbastanza grande.

Quindi un ordine è una lista, una distinta base è una lista (o una lista di liste, che sarebbe ancora più un incubo da implementare "lateralmente"). Ma una nota / commento e una poesia non lo sono.

Dati due tabelle; li chiameremo Person e Task, ognuno con il proprio ID (PersonID, TaskID) ... l'idea di base è creare un terzo tavolo per unirli insieme. Chiameremo questa tabella PersonToTask. Al minimo dovrebbe avere il proprio ID, così come gli altri due Quindi quando si tratta di assegnare qualcuno a un compito; non avrai più bisogno di AGGIORNARE la tabella Persona, devi solo INSERIRE una nuova riga in PersonToTaskTable. E la manutenzione diventa più semplice: la necessità di eliminare un'attività diventa semplicemente DELETE basata su TaskID, non più l'aggiornamento della tabella Persona e l'analisi associata.

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

Che ne dici di un semplice rapporto o di chi è assegnato a un compito?

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

Ovviamente potresti fare molto di più; un TimeReport può essere eseguito se hai aggiunto i campi DateTime per TaskAssigned e TaskCompleted. Tutto dipende da te

Potrebbe funzionare se si dice di avere chiavi primarie leggibili dall'uomo e di volere un elenco di attività # senza dover affrontare la natura verticale di una struttura di tabella. cioè molto più facile leggere la prima tabella.

------------------------  
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
------------------------

La domanda sarebbe quindi: l'elenco delle attività deve essere memorizzato o generato su richiesta, che dipende in gran parte da requisiti quali: la frequenza con cui è necessario l'elenco, la precisione di quante righe di dati esistono, come verranno utilizzati i dati , ecc ... dopo di che si analizzano i trade off per l'esperienza dell'utente e per soddisfare i requisiti.

Ad esempio, confrontando il tempo necessario per richiamare le 2 righe e l'esecuzione di una query che genererebbe le 2 righe. Se richiede molto tempo e l'utente non ha bisogno dell'elenco più aggiornato (* prevede meno di 1 modifica al giorno), potrebbe essere archiviato.

O se l'utente ha bisogno di una registrazione storica delle attività assegnate a loro avrebbe anche senso se la lista fosse stata archiviata. Quindi dipende davvero da quello che stai facendo, mai dire mai.

Se è "non ok", allora è abbastanza brutto che ogni sito Wordpress abbia mai una lista in wp_usermeta con wp_capabilities in una riga, elenco respinti_wp_pointers in una riga, e altri ...

In effetti in casi come questo potrebbe essere migliore per la velocità dato che quasi sempre vorrai la lista . Ma Wordpress non è noto per essere l'esempio perfetto di best practice.