Skip to content

Il modo migliore per creare un array annidato da tabelle: query multiple/loop VS stile a query singola/loop

È essenziale comprendere correttamente il codice prima di utilizzarlo nel tuo lavoro e se hai qualcosa da contribuire puoi dirlo nei commenti.

Soluzione:

Preliminare

Innanzitutto, grazie per l'impegno profuso nella spiegazione del problema e per la formattazione. È bello vedere qualcuno che è chiaro su ciò che sta facendo e su ciò che sta chiedendo.

Ma va notato che questo, di per sé, costituisce una limitazione: siete convinti che questa sia la soluzione corretta e che, con qualche piccola correzione o guida, funzionerà. Questo non è corretto. Devo quindi chiedervi di rinunciare a questa nozione, di fare un grande passo indietro e di vedere (a) l'intero problema e (b) la mia risposta senza questa nozione.

Il contesto di questa risposta è:

  • tutte le considerazioni esplicite che lei ha fatto, che sono molto importanti e che non ripeterò.

  • le due più importanti delle quali sono: cosa migliore pratica e cosa farei nella vita reale

Questa risposta è radicata negli Standard, l'ordine superiore di, o quadro di riferimento per, la migliore pratica. Questo è ciò che fa, o dovrebbe fare, il mondo commerciale Client/Server.

Questo problema, questo intero spazio problematico, sta diventando un problema comune. Darò qui una considerazione completa, rispondendo così anche a un'altra domanda del SO. Pertanto, potrebbe contenere qualche dettaglio in più rispetto a quanto richiesto. Se così fosse, vi prego di scusarmi.

Considerazione

  1. Il database è una risorsa basata sul server, condivisa da molti utenti. In un sistema online, il database cambia continuamente. Contiene un'unica versione della verità (distinta da un unico fatto in un unico luogo, che è una questione separata di normalizzazione) di ciascun fatto.

    • il fatto che alcuni sistemi di database non hanno un'architettura server, e che quindi la nozione di server in tali software è falsa e fuorviante, sono punti distinti, ma che si notano.
  2. Da quello che ho capito, JSON e le strutture simili a JSON sono necessarie per "ragioni di performance", proprio perché il "server" non funziona, non può funzionare come un server. Il concetto è quello di memorizzare i dati nella cache su ogni (singolo) client, in modo da non doverli recuperare continuamente dal "server".

    • Questo apre una serie di problemi. Se non lo si progetta e implementa correttamente, i vermi invaderanno l'applicazione.

    • Un'implementazione di questo tipo è una grave violazione dell'architettura client/server, che consente un codice semplice su entrambi i lati e una distribuzione appropriata dei componenti software e dei dati, in modo che i tempi di implementazione siano ridotti e l'efficienza elevata.

    • Inoltre, un'implementazione di questo tipo richiede un notevole sforzo di implementazione ed è complessa, essendo composta da molte parti. Ognuna di queste parti deve essere progettata in modo appropriato.

    • Il web e i molti libri scritti in questo settore offrono un mix confuso di metodi, commercializzati sulla base di una presunta semplicità.e; chiunque può fare qualsiasi cosa; freeware può fare qualsiasi cosa; ecc. Non ci sono basi scientifiche per nessuna di queste proposte.

Non architettura e sotto-standard

Come è evidente, avete imparato che alcuni approcci alla progettazione dei database non sono corretti. Avete incontrato uno problema, uno che quel consiglio è falso. Non appena risolvete questo problema, il problema successivo, che in questo momento non vi è chiaro, verrà alla luce. Le nozioni sono un insieme infinito di problemi.

Non elencherò tutte le false nozioni che a volte vengono sostenute. Confido che, man mano che procederete nella mia risposta, vi accorgerete che una dopo l'altra le nozioni commercializzate sono false.

Le due linee di fondo sono:

  1. Le nozioni violano gli standard di architettura e progettazione, in particolare l'architettura client/server.e; Architettura aperta principi di ingegneria e, in misura minore in questo particolare problema, principi di progettazione dei database.

  2. Il che porta persone come voi, che stanno cercando di fare un lavoro onesto, a essere ingannate nell'implementazione di semplici nozioni, che si trasformano in implementazioni massicce. Implementazioni che non funzioneranno mai del tutto, quindi richiedono una manutenzione continua e sostanziale, e alla fine saranno sostituite, all'ingrosso.

Architettura

Il principio centrale che viene violato è: non duplicare mai nulla. Nel momento in cui si ha una posizione in cui i dati sono duplicati (a causa della cache o della replica o di due applicazioni monolitiche separate, ecc. sarà non sarà più sincronizzato in una situazione online. Il principio è quindi quello di evitare di farlo.

  • Certo, per i software di terze parti più seri, come ad esempio gli strumenti di reportistica, è possibile che i dati basati sul server vengano memorizzati nella cache del client. Ma si noti che hanno impiegato centinaia di anni di lavoro per implementarlo correttamente, tenendo in debita considerazione quanto detto sopra. Il vostro non è un software di questo tipo.

Piuttosto che fornire una lezione sui principi che devono essere compresi, o sui mali e i costi di ogni errore, il resto di questa risposta fornisce la richiesta cosa fareste nella vita reale utilizzando il metodo architettonico corretto (un passo avanti rispetto a delle migliori pratiche).

Architettura 1

Non confondere

  • il dati che devono essere normalizzati

con

  • il risultato del set che, per definizione, è la visione appiattita ("de-normalizzata" non è del tutto corretto) dei dati.

I dati, dato che sono normalizzati, saranno non contenere valori duplicati o gruppi ripetuti. L'insieme dei risultati sarà conterrà valori duplicati; gruppi ripetuti. Questo è un pedone.

  • Si noti che la nozione di insiemi annidati (o relazioni annidate), che a mio avviso non è un buon consiglio, si basa proprio su questa confusione.

  • Per quarantacinque anni, dall'avvento dell'interfaccia grafica RM non sono stati in grado di differenziare le relazioni di base (per le quali la Normalizzazione si applica) dalle relazioni derivate (per le quali la Normalizzazione non si applica).

  • Due di questi sostenitori stanno attualmente mettendo in discussione la definizione di Prima Forma Normale. La 1NF è il fondamento delle altre NF; se la nuova definizione viene accettata, tutte le NF saranno rese prive di valore. Il risultato sarebbe che la Normalizzazione stessa (scarsamente definita in termini matematici, ma chiaramente intesa come scienza dai professionisti) verrebbe gravemente danneggiata, se non distrutta.

Architettura 2

Esiste un principio scientifico o ingegneristico vecchio di secoli: il contenuto (i dati) deve essere separato dal controllo (gli elementi del programma). Questo perché l'analisi, la progettazione e l'implementazione delle due cose sono completamente diverse. Questo principio non è meno importante nelle scienze del software, dove ha un'articolazione specifica.

Per essere breve (ah ah), invece di un discorso, darò per scontato che abbiate capito:

  • Che esiste un confine scientificamente richiesto tra dati ed elementi di programma. Confondendoli si ottengono oggetti complessi, soggetti a errori e difficili da mantenere.

    • La confusione di questo principio ha raggiunto proporzioni epidemiche nel mondo OO/ORM, con conseguenze che si estendono in lungo e in largo.

    • Solo i professionisti lo evitano. Gli altri, la grande maggioranza, accettano la nuova definizione come "normale" e passano la vita a risolvere problemi che semplicemente non ci sono.

  • La superiorità architettonica, il grande valore, dei dati memorizzati e presentati in forma tabellare per il dottor E F Codd Modello relazionale. Esistono regole specifiche per la normalizzazione dei dati.

  • E soprattutto, è possibile determinare quando le persone che scrivono e commercializzano libri consigliano metodi non relazionali o antirelazionali.

Architettura 3

Se i dati vengono memorizzati nella cache del client:

  1. Mettere in cache il minimo indispensabile.

    Ciò significa memorizzare nella cache solo i dati che non cambiano nell'ambiente online. Ciò significa solo le tabelle di riferimento e di ricerca, le tabelle che popolano i classificatori di livello superiore, i menu a tendina, ecc.

  2. Valuta

    Per ogni tabella che viene memorizzata nella cache, è necessario disporre di un metodo per (a) determinare che i dati memorizzati nella cache sono diventati obsoleti rispetto alla versione unica della verità esistente sul server e (b) aggiornarli dal server, (c) tabella per tabella.

    In genere, si tratta di un processo in background che viene eseguito ogni (e) cinque minuti, che interroga la Data di aggiornamento massima per ogni tabella memorizzata nella cache sul client rispetto alla Data di aggiornamento sul server e, se è cambiata, aggiorna la tabella, e tutte le sue tabelle figlie, quelle che dipendono dalla tabella modificata.

    Questo, ovviamente, richiede che si abbia una tabella UpdatedDateTime su ogni tabella. Questo non è un peso, perché è comunque necessario per le transazioni OLTP ACID (se si ha un vero database, invece di un mucchio di file non standard).

Il che significa, in realtà, non replicare mai, l'onere di codifica è proibitivo.

Architettura 4

Nel mondo sub-commerciale, non server, so che alcuni consigliano il reverse caching di "tutto".

  • Questo è l'unico modo in cui programmi come PostgreSQL possono essere utilizzati in un sistema multiutente.

  • Si ottiene sempre ciò che si paga: si pagano noccioline, si ottengono scimmie; si paga zero, si ottiene zero.

Il corollario dell'architettura 3 è che, se i dati vengono memorizzati nella cache sul client, non si devono memorizzare nella cache le tabelle che cambiano frequentemente. Queste sono le tabelle delle transazioni e della cronologia. L'idea di mettere in cache tali tabelle, o tutte le tabelle, sul client è completamente fallita.

In una vera distribuzione client/server, a causa dell'uso degli standard applicabili, per ogni finestra di dati, l'applicazione dovrebbe interrogare solo le righe necessarie per quella particolare esigenza, in quel particolare momento, in base al contesto o ai valori dei filtri, ecc. L'applicazione non deve mai caricare l'intera tabella.

Se lo stesso utente, utilizzando la stessa finestra, ne ispezionasse il contenuto 15 minuti dopo la prima ispezione, i dati non sarebbero più aggiornati di 15 minuti.

  • Per le piattaforme freeware/shareware/vapourware, che si definiscono per l'assenza di un'architettura server, e quindi per il risultato che le prestazioni sono inesistenti, certo, bisogna mettere in cache più delle tabelle minime sul client.

  • Se lo fate, dovete tenere conto di tutto quanto detto sopra e implementarlo correttamente, altrimenti la vostra applicazione si romperà e le conseguenze porteranno gli utenti a chiedere la vostra cessazione. Se c'è più di un utente, avranno la stessa causa e presto formeranno un esercito.

Architettura 5

Ora arriviamo a come si memorizzano nella cache le tabelle scelte con cura sul client.

Si noti che i database crescono, vengono estesi.

  • Se il sistema è rotto, un fallimento, crescerà in piccoli incrementi e richiederà un grande sforzo.

  • Se il sistema è un piccolo successo, crescerà in modo esponenziale.

  • Se il sistema (ogni database e ogni applicazione, separatamente) è progettato e implementato bene, le modifiche saranno facili e i bug saranno pochi.

Pertanto, tutti i componenti dell'applicazione devono essere progettati correttamente, per essere conformi agli standard applicabili, e il database deve essere completamente normalizzato. Questo a sua volta riduce al minimo l'effetto delle modifiche al database sull'applicazione e viceversa.

  • L'applicazione sarà costituita da oggetti semplici e non complessi, facili da mantenere e modificare.

  • Per i dati che vengono memorizzati nella cache del client, si utilizzeranno array di qualche tipo: istanze multiple di una classe in una piattaforma OO; DataWindows (TM, cercatelo su Google) o simili in un 4GL; semplici array in PHP.

(A parte. Si noti che ciò che le persone in situazioni come la vostra producono in un anno, lo producono fornitori professionali che utilizzano una piattaforma SQL commerciale, un 4GL commerciale e che rispettano l'architettura e gli standard.)

Architettura 6

Supponiamo che abbiate compreso tutto quanto sopra e che ne apprezziate il valore, in particolare le Architetture 1 e 2.

  • Se così non fosse, fermatevi qui e fate domande, non proseguite oltre.

Ora che abbiamo stabilito il contesto completo, possiamo affrontare il nocciolo del vostro problema.

  • In quegli array nell'applicazione, perché mai si dovrebbero memorizzare viste appiattite dei dati?

    • e di conseguenza, pasticciare e agonizzare con i problemi
  • invece di memorizzare copie delle tabelle normalizzate?

Risposta

  1. Non duplicare mai nulla che possa essere derivato. Questo è un principio architettonico, non limitato alla normalizzazione in un database.

  2. Non unire mai nulla.

    Se lo fate, sarete creando:

    • la duplicazione dei dati, e in massa, sul client. Il client non solo sarà grasso e lento, ma sarà ancorato al pavimento con la zavorra dei dati duplicati.

    • codice aggiuntivo, completamente inutile

    • complessità del codice

    • codice fragile, che dovrà essere costantemente modificato.

    Questo è il problema preciso di cui soffrite, una conseguenza del metodo, che sapete intuitivamente essere sbagliato, che ci deve essere un modo migliore. Sapete che è un problema generico e comune.

    Notate anche che quel metodo, quel codice, costituisce un'ancora mentale per voi. Guardate il modo in cui l'avete formattato e presentato così bene: è importante per voi. Sono riluttante a informarvi di tutto questo.

    • Una riluttanza facilmente superabile, grazie al suo atteggiamento sincero e schietto e alla consapevolezza che non ha inventato lei questo metodo.
  3. In ogni segmento di codice, al momento della presentazione, come e quando richiesto:

    a. Nel contesto commerciale Client/Server
    Eseguire una query che si unisce a le tabelle semplici, normalizzate e non duplicate e recupera solo le righe qualificate. In questo modo si ottengono i valori attuali dei dati. L'utente non vede mai dati obsoleti. In questo caso, si utilizzano spesso le viste (viste appiattite di dati normalizzati).

    b. Nel contesto sub-commerciale non server
    Creare un array temporaneo di risultati e unire gli array semplici, non duplicati (copie delle tabelle che sono memorizzate nella cache), e popolarli con le sole righe qualificate, provenienti dagli array di origine. La valuta è mantenuta dal processo in background.

    • Usare le chiavi per formare le giunzioni tra gli array, esattamente come le chiavi sono usate per formare le giunzioni nelle tabelle relazionali del database.

    • Distruggere questi componenti quando l'utente chiude la finestra.

    • Una versione intelligente eliminerebbe l'array dei risultati e unirebbe gli array di origine tramite le chiavi, limitando il risultato alle righe qualificate.

Oltre a essere architettonicamente scorretti, gli array annidati o gli insiemi annidati o le strutture JSON o simili a JSON non sono semplicemente necessari. Questa è la conseguenza della confusione del Principio dell'Architettura 1.

  • Se si sceglie di usare tali strutture, allora usarle solo per gli array temporanei di risultati.

Infine, confido che questo discorso dimostri che n tabelle non è un problema. E, cosa più importante, che m nella gerarchia dei dati, il "nesting", non è un problema.

Risposta 2

Ora che ho fornito il contesto completo (e non prima), il che elimina le implicazioni della sua domanda e la rende generica e kernel.

La domanda riguarda QUALSIASI server-side/db relazionale. [Which is better]:

2 cicli, 5 semplici interrogazioni "SELECT

1 ciclo, 1 query "JOIN

Gli esempi dettagliati che avete fornito non sono descritti in modo accurato. Le descrizioni accurate sono:

  • La vostra opzione 1
    2 loop, ogni loop per il caricamento di ogni array
    1 query SELECT a tabella singola per ciclo
    (eseguita n x m volte ... solo il loop più esterno è una singola esecuzione)

  • Opzione 2
    1 query SELECT unita eseguita una volta
    seguita da 2 cicli, ogni ciclo per caricare ogni array

Per le piattaforme SQL commerciali, nessuna delle due, perché non si applica.

  • Il server SQL commerciale è un motore di elaborazione degli insiemi. Usare una query con qualsiasi join necessario, che restituisca un insieme di risultati. Non passate mai attraverso le righe usando un ciclo, che riduce il motore di elaborazione degli insiemi a un sistema ISAM precedente al 1970. Utilizzate una Vista, nel server, poiché offre le prestazioni più elevate e il codice si trova in un unico posto.

Tuttavia, per le piattaforme non commerciali, non server, dove:

  • il vostro "server" è non un motore di elaborazione dei set, cioè restituisce singole righe, quindi è necessario recuperare ogni riga e riempire l'array, manualmente o

  • il vostro "server" fa non fornisce il binding client/server, cioè non fornisce le strutture sul client per legare il set di risultati in arrivo a un array ricevente, e quindi si deve passare attraverso il set di risultati restituito, riga per riga, e riempire l'array manualmente,

come nel vostro esempio allora la risposta è, con ampio margine, la vostra opzione 2.

Si prega di considerare attentamente e di commentare o fare domande.

Risposta al commento

Diciamo che devo stampare questo json (o un'altra pagina html) su uno STOUT (esempio: una risposta http a: GET /allUsersPhoneNumbers. È solo un esempio per chiarire cosa mi aspetto di ottenere), dovrebbe restituire questo json. Ho una funzione php che ha ottenuto questi 2 set di risultati (1). ora dovrebbe stampare questo json - come dovrei fare? questo report potrebbe essere un mese di stipendio di un dipendente per un intero anno, e così via. in un modo o nell'altro, ho bisogno di raccogliere queste informazioni e rappresentarle in una rappresentazione "JOIN".

Forse non sono stato abbastanza chiaro.

  1. In sostanza, non usare JSON a meno che non sia assolutamente necessario. Il che significa inviare a qualche sistema che lo richiede, il che significa che il sistema ricevente, e questa richiesta è stupida.

  2. Assicurarsi che il proprio sistema non faccia tali richieste ad altri.

  3. Mantenere i dati normalizzati. Sia nel database che in qualsiasi elemento del programma che scrivete. Ciò significa (in questo esempio) utilizzare una SELECT per tabella o array. Questo per motivi di caricamento, in modo da poterli consultare e ispezionare in qualsiasi punto del programma.

  4. Quando si ha bisogno di un join, bisogna capire che si tratta di un join:

    • un insieme di risultati; una relazione derivata; una vista
    • quindi temporanea, esiste per tutta la durata dell'esecuzione di quell'elemento, solo che

    a. Per le tabelle, unirle nel modo consueto, tramite le chiavi. Una query che unisce due (o più) tabelle.

    b. Per gli array, unire gli array nel programma, nello stesso modo in cui si uniscono le tabelle nel database, tramite le chiavi.

  5. Per l'esempio che avete fatto, che è una risposta a una qualche richiesta, capite innanzitutto che si tratta della categoria [4]e poi soddisfarla.

Perché prendere in considerazione JSON?
Che cosa ha a che fare JSON con questo?

JSON è frainteso e le persone sono interessate al fattore "wow". È una soluzione che cerca un problema. A meno che non abbiate quel problema, non ha alcun valore.
Consultate questi due link:
Copter - Cos'è JSON
StackOverflow - Cos'è JSON

Ora, se si capisce che, è principalmente per i feed in entrata. Mai per quelli in uscita. Inoltre, richiede di essere analizzato, decostruito, ecc. prima di poter essere utilizzato.

Ricordare:

Devo raccogliere queste informazioni e rappresentarle in una rappresentazione "JOIN".

Sì, questo è pedonale. Unito fa non significa JSONed.

Nel vostro esempio, il destinatario si aspetta una vista appiattita (ad esempio, un foglio di calcolo), con tutte le celle riempite, e sì, per gli utenti con più di un numero di telefono, i loro dati utente saranno ripetuti sulla seconda e successiva riga del set di risultati. Per qualsiasi tipo di print, ad esempio per il debug, voglio una vista appiattita. È solo un:

    SELECT ... FROM Person JOIN PhoneNumber

E restituirlo. Oppure, se si soddisfa la richiesta da array, unire gli array Persona e Numero di telefono, il che potrebbe richiedere un array temporaneo di risultati, e restituirlo.

per favore, non ditemi che dovreste ottenere solo 1 utente alla volta, ecc. ecc.

Corretto. Se qualcuno vi dice di regredire all'elaborazione procedurale (cioè riga per riga, in un ciclo WHILE), laddove il motore o il vostro programma ha un'elaborazione di set (cioè elabora un intero set con un solo comando), questo lo segna come una persona che non dovrebbe essere ascoltata.

Ho già detto che l'opzione 2 è corretta, l'opzione 1 è errata. Questo per quanto riguarda il GET o il SELECT.

D'altra parte, per i linguaggi di programmazione che non hanno capacità di elaborazione dei set (cioè non possono stampare/impostare/ispezionare un array in un unico comando), o per i "server" che non forniscono il binding degli array lato client, è necessario scrivere dei loop, un loop per ogni profondità della gerarchia dei dati (nel vostro esempio, due loop, uno per Persona e uno per Numero di telefono per Utente).

  • È necessario farlo per analizzare un oggetto JSON in arrivo.
  • Bisogna farlo per caricare ogni array dall'insieme dei risultati restituiti dall'opzione 2.
  • Per stampare ogni array dall'insieme di risultati restituito nell'opzione 2.

Risposta al commento 2

Ho pensato di dover restituire un risultato rappresentato in una versione annidata (diciamo che sto stampando il report sulla pagina), json era solo un esempio per tale rappresentazione.

Non credo che tu abbia capito il ragionamento e le conclusioni che ho fornito in questa risposta.

  • Per la stampa e la visualizzazione, mai nido. Stampa una vista appiattita le righe restituite dalla SELECT in base all'opzione 2. Questo è ciò che facciamo da 31 anni quando stampiamo o visualizziamo i dati in modo relazionale. È più facile leggere, eseguire il debug, cercare, trovare, piegare, pinzare, mutilare. Non è possibile fare nulla con un array annidato, se non guardarlo e dire cavolo, questo è interessante.

Codice

Caveat

Preferirei prendere il vostro codice e modificarlo, ma in realtà, guardando il vostro codice, non è ben scritto o strutturato, non può essere ragionevolmente modificato. In secondo luogo, se lo usassi, sarebbe un pessimo strumento didattico. Dovrò quindi fornirvi codice fresco e pulito, altrimenti non imparerete i metodi corretti.

Questi esempi di codice seguono i miei consigli, quindi non li ripeterò. E questo va ben oltre la domanda iniziale.

  • Interrogazione e stampa

    La vostra richiesta, utilizzando l'opzione 2. Una SELECT eseguita una volta. Seguita da un ciclo. Che si può "abbellire" se si vuole.

Puoi aiutarci nella nostra missione lasciando un commento e valutandolo, te ne siamo grati.



Utilizzate il nostro motore di ricerca

Ricerca
Generic filters

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.