singola tabella fissa con colonne multiple vs tabelle astratte flessibili

Mi chiedevo se hai un sito Web con una dozzina di diversi tipi di annunci (negozi, ristoranti, club, hotel, eventi) che richiedono campi diversi, c’è un vantaggio nel creare una tabella con colonne definite come
Negozio di esempio:

shop_id | name | X | Y | city | district | area | metro | station | address | phone | email | website | opening_hours 

O un approccio più astratto simile a questo:

 object_id | name --------------- 1 | Messy Joe's 2 | Bate's Motel type_id | name --------------- 1 | hotel 2 | restaurant object_id | type_id --------------- 1 | 2 2 | 1 field_id | name | field_type --------------- 1 | address | text 2 | opening_hours | date 3 | speciality | text type_id | field_id --------------- 1 | 1 1 | 2 2 | 1 2 | 3 object_id | field_id | value 1 | 1 | 1st street.... 1 | 3 | English Cuisine 

Ovviamente può essere più astratto se i valori sono predefiniti (Esempio: le specialità potrebbero avere una propria lista)

Se prendo l’approccio astratto, può essere molto flessibile, ma le query saranno più complesse con molti join. Ma non so se questo influisce sulle prestazioni, eseguendo queste query ‘più complesse’.

Sarei interessato a sapere quali sono gli aspetti positivi e negativi di entrambi i metodi. Posso solo immaginare per me stesso, ma non ho l’esperienza per confermare questo.

Alcuni aspetti devono essere chiariti e risolti prima di poter entrare in una discussione ragionevole.

Risoluzione pre-requisito

  1. etichette
    In una professione che richiede precisione, è importante che usiamo etichette precise, per evitare confusione e in modo da poter comunicare senza dover utilizzare descrizioni e qualificazioni prolissi.
    .
    Quello che hai pubblicato come FixedTables, è non normalizzato. Giusto, potrebbe essere un tentativo di terza forma normale, ma in realtà è un file piatto, non normalizzato (non “denormalizzato). Quello che hai pubblicato come AbstractTables è, per la precisione, Entity-Attribute-Value , che è quasi , ma non del tutto, sesta forma normale, ed è quindi più normalizzato di 3NF. Supponendo che sia fatto correttamente, ovviamente.

    • Il file flat non normalizzato non è “denormalizzato”. È pieno zeppo di duplicazione (nulla è stato fatto per rimuovere gruppi ripetuti e colonne duplicate o per risolvere dipendenze) e Null, è un maiale performante in molti modi e previene la concorrenza.

    • Per essere Denormlaised, deve prima essere Normalizzato, e quindi la Normalizzazione si è attenuata un po ‘per una buona ragione. Dal momento che non è normalizzato in primo luogo, non può essere denormalizzato. È semplicemente non normalizzato.

    • Non si può dire che sia denormalizzato “per le prestazioni”, perché essendo un maiale performante, è proprio l’antitesi della performance. Bene, hanno bisogno di una giustificazione per la mancanza di design formalizzato], e “per le prestazioni” lo è. Anche il più piccolo scrutinio formale ha messo in luce il travisamento (ma pochissime persone possono fornirlo, quindi rimane nascosto fino a quando un estraneo non riesce a risolvere, hai indovinato, l’enorme problema delle prestazioni).

    • Le strutture normalizzate funzionano molto meglio delle strutture non normalizzate. Strutture più normalizzate (EAV / 6NF) funzionano meglio di strutture meno normalizzate (3NF / 5NF).

    • Sono d’accordo con la spinta di OMG Ponies, ma non con le loro etichette e definizioni

    • piuttosto che dire ” non” denormalizzare “a meno che tu non debba” , sto dicendo, “Normalizza fedelmente, punto” e “se c’è un problema di prestazioni, non hai normalizzato correttamente” .
      .
  2. wiki
    Le voci su Normal Form e Normalization sono uno scherzo completo. Nello specifico, le definizioni non sono corrette; confondono le forms normali; sono senza tracce nel processo di normalizzazione; e danno uguale peso a NF assurdi o discutibili che sono stati ridimensionati molto tempo fa. Il risultato è, Wiki aggiunge a un argomento già confuso e raramente compreso. Quindi non perdere tempo.
    .
    Tuttavia, al fine di progredire, senza quella referenza che pone un ostacolo, lasciatemi dire questo.

    • La definizione di 3NF è stabile e non è cambiata.
    • C’è molta confusione tra i NF tra 3NF e 5NF. La verità è che questa è un’area che è progredita negli ultimi 15 anni; e molte org, accademici e venditori con i loro prodotti con limitazioni, sono saltati per creare un nuovo “Normal Form” per convalidare le loro offerte. Tutti servono interessi commerciali e accademicamente non credono. 3NF nel suo stato originale non controllato intendeva e garantiva determinati attributi.
    • La sum totale è, 5NF è oggi, ciò che 3NF doveva essere 15 anni fa, e si possono saltare le battute commerciali ei circa dodici NFs “speciali” (commerciali e pseudo-accademici) nel mezzo, alcuni dei quali sono identificato in Wiki, e anche in termini confusi.
      .
  3. Dal momento che sei stato in grado di comprendere e implementare l’EAV nel tuo post, non avrai alcun problema a capire quanto segue. Ovviamente un vero modello relazionale è pre-requisito, chiavi forti, ecc. La quinta forma normale è, dato che stiamo saltando il quarto:

    • Terza forma normale
      • che in termini semplici e definitivi, ogni colonna non chiave in ogni tabella ha una relazione 1: 1 con la chiave primaria della tabella,
      • e a nessun’altra colonna non chiave
    • Duplicazione zero dei dati (il risultato, se la normalizzazione è progredita diligentemente, non ottenuta dall’intelligenza o dall’esperienza da sola, o lavorando verso di essa come objective senza il processo formale)
    • no Update Anomalies (quando si aggiorna una colonna da qualche parte, non è necessario aggiornare la stessa colonna localizzata altrove, la colonna esiste in un unico punto).
      .
  4. La sesta forma normale è ovviamente la quinta forma normale, oltre a:

    • Eliminazione di dati mancanti (colonne). Questa è l’unica vera soluzione al Problema Nullo (chiamato anche Gestione dei Valori mancanti), e il risultato è un database senza Null. (Può essere fatto a 5NF con standard e sostituti Null ma non è ottimale.) Il modo in cui interpreti e mostri i valori mancanti è un’altra storia.
      .
  5. EAV vs sesta forma normale
    Tutti i database che ho scritto, tranne uno, sono 5NF puri. Ho lavorato con (amministrato, risolto, migliorato) un paio di database EAV e ho implementato un vero database 6NF. L’EAV è un’implementazione libera di 6NF, spesso fatta da persone che non hanno una buona conoscenza della Normalizzazione e dei NF, ma che possono vedere il valore in, e hanno bisogno della flessibilità di EAV. Sei un esempio perfetto. La differenza è questa: perché è allentato e poiché gli implementatori non hanno un riferimento (6NF) per essere fedeli, implementano solo ciò di cui hanno bisogno e lo scrivono tutto in codice; che finisce per essere un modello incoerente.
    .
    Considerando che, una pura implementazione 6NF ha un puro punto di riferimento accademico, e quindi è solitamente più stretta e coerente. In genere questo si presenta in due elementi visibili:
    • 6NF ha un catalogo per contenere i metadati e tutto è definito nei metadati, non nel codice. EAV non ne ha uno, tutto è in codice (gli implementatori tengono traccia degli oggetti e degli attributi). Ovviamente un catalogo facilita l’aggiunta di colonne, la navigazione e consente di creare utilità.
    • 6NF, una volta capito, fornisce la vera soluzione al Null Problem. Gli implementatori di EAV, poiché sono assenti dal contesto 6NF, gestiscono i dati mancanti nel codice, in modo incoerente o peggio, consentono i Null nel database. Gli implementatori 6NF non consentono i Null e gestiscono i Dati mancanti in modo coerente ed elegante, senza richiedere costrutti di codice (per la gestione Null, è comunque necessario codificare i dati mancanti ovviamente).
      .
      Per esempio. Per i database 6NF con un catalogo, dispongo di un set di procs che [ri] generi l’SQL necessario per eseguire tutti i SELECTs e fornisco Views in 5NF per tutti gli utenti, quindi non hanno bisogno di conoscere o comprendere la struttura 6NF sottostante . Sono cacciati dal catalogo. Quindi i cambiamenti sono facili e automatizzati. I tipi EAV lo fanno manualmente, a causa dell’assenza del catalogo.

Ora possiamo iniziare il

Discussione

“Ovviamente può essere più astratto se i valori sono predefiniti (Esempio: le specialità potrebbero avere una propria lista)”

Sicuro. Ma non diventare troppo “astratto”. Mantenere la coerenza e implementare tali elenchi nello stesso modo EAV (o 6NF) delle altre liste.

“Se prendo l’approccio astratto può essere molto flessibile, ma le query saranno più complesse con molti join, ma non so se questo influisce sulle prestazioni, eseguendo queste query ‘più complesse’.”

  1. Le unioni sono pedonali in database relazionali. Il problema non è il database, il problema è che SQL è ingombrante quando si gestiscono i join, specialmente le chiavi composte.
  2. I database EAV e 6NF hanno più join, che proprio come pedoni, né più né meno. Se devi codificare ogni SELECT manualmente, certo, l’ingombrante diventa davvero ingombrante.
  3. L’intero problema può essere eliminato con (a) andando con 6NF su EAV e (b) implementando un catalogo, dal quale è ansible (c) generare tutto l’SQL di base. Elimina anche un’intera class di errori.
  4. È un mito comune che Joins abbia in qualche modo un costo. Totalmente falso. Il join viene implementato in fase di compilazione, non c’è nulla di sostanziale nei cicli della CPU ‘costati’. Il problema è la dimensione dei tavoli che vengono uniti, non il costo del join tra quelle stesse tabelle. Unendo due tabelle con milioni di righe ciascuna, su una relazione PK⇢FK corretta, ognuna delle quali ha gli indici appropriati (Unico sul lato genitore [FK]; Unico sul lato Bambino) è istantanea; ; dove l’indice Child non è univoco, ma almeno la colonna principale è valida, è più lenta; dove non c’è indice utile, ovviamente è molto lento. Nessuno di questi ha a che fare con il costo di Join. Dove vengono restituite molte righe, il collo di bottiglia sarà la rete e il layout del disco; non l’elaborazione del join.
  5. Quindi puoi ottenere come “complesso” come vuoi, non ci sono costi, SQL può gestirlo.

Sarei interessato a sapere quali sono gli aspetti positivi e negativi di entrambi i metodi. Posso solo immaginare per me stesso, ma non ho l’esperienza per confermare questo.

  1. 5NF (o 3NF per coloro che non hanno fatto la progressione) è il più semplice e migliore, in termini di implementazione, facilità d’uso (sviluppatori e utenti), manutenzione. Lo svantaggio è che, ogni volta che aggiungi una colonna, devi modificare la struttura del database (tabella DDL). Questo va bene in alcuni casi, ma non nella maggior parte dei casi, a causa del cambio di controllo in atto, abbastanza oneroso. In secondo luogo, è necessario modificare il codice esistente (il codice che gestisce la nuova colonna non conta, perché è un imperativo): laddove vengono implementati standard validi, ciò viene ridotto al minimo; dove sono assenti, l’ambito è imprevedibile.

  2. EAV (che è ciò che hai pubblicato), consente di aggiungere colonne senza modifiche DDL. Questa è la ragione per cui le persone lo scelgono. (il codice che gestisce la nuova colonna non conta, perché è un imperativo). Se implementato correttamente, non influirà sul codice esistente; se no, lo farà. Ma hai bisogno di sviluppatori capaci di EAV. Quando l’EAV è implementato male, è abominevole, un pasticcio peggiore di 5NF fatto male, ma non peggiore di Unnormalised che è ciò che la maggior parte dei database ci sono (travisato come “Denormalised for performance”). ovviamente, è ancora più importante (rispetto a 5NF / 3NF) mantenere un forte contesto di Transaction, perché le colonne sono molto più distribuite. Allo stesso modo, è essenziale conservare l’integrità referenziale dichiarativa: i problemi che ho visto sono dovuti in gran parte agli sviluppatori che rimuovono DRI perché è diventato “troppo difficile da mantenere”, il risultato è stato, come potete immaginare, una madre di dati heap con duplicati di righe e colonne 3NF / 5NF in tutto il luogo. E maneggevolezza del Null incoerente.

  3. Non c’è differenza nelle prestazioni, supponendo che il server sia stato ragionevolmente configurato per lo scopo previsto. (Ok, ci sono ottimizzazioni specifiche che sono possibili solo in 6NF, che non sono possibili in altri NF, ma penso che sia al di fuori dell’ambito di questa discussione.) E ancora, l’EAV fatto male può causare colli di bottiglia inutili, non più di denormalizzati.

  4. Certo, se vai con EAV, ti sto raccomandando più formalità; compra il quid completo; andare con 6NF; implementare un catalogo; utilità per produrre SQL; Visualizzazioni; gestire costantemente i dati mancanti; eliminare del tutto i Null. Questo riduce la tua vulnerabilità alla qualità dei tuoi sviluppatori; possono dimenticare le emissioni esoteriche EAV / 6NF, utilizzare Views e concentrarsi sulla logica dell’app.

Perdonate il lungo post.

Nella tua domanda, hai presentato almeno due problemi principali allo stesso tempo. Questi due problemi sono EAV e gen-spec.

Per prima cosa, parliamo di EAV. L’ultima tabella (object_id, field_id, value) è essenzialmente un EAV. C’è un rialzo dell’EAV e uno svantaggio dell’EAV. Il lato positivo è che la struttura è così generica da poter contenere quasi tutti i dati che descrivono quasi tutti gli argomenti. Ciò significa che è ansible procedere alla progettazione e all’implementazione senza analisi dei dati e senza comprensione dell’argomento trattato, senza preoccuparsi di ipotesi errate. Il lato negativo è che al momento del recupero, devi fare l’analisi dei dati che hai saltato prima di build il database, in modo da ottenere query che significano qualcosa. Questo è molto più serio della semplice efficienza di recupero. Ma avrai anche problemi terribili con l’efficienza di recupero. Ci sono solo due modi per apprendere su questo trabocchetto: vivilo attraverso o leggi su di esso da coloro che lo hanno. Raccomando la lettura.

Secondo, hai un caso specifico. La tua tabella (object_id, type_id) cattura uno schema gen-spec (generalizzazione-specializzazione), insieme alle relative tabelle. Se dovessi generalizzare tra hotel e ristoranti, potrei chiamarlo qualcosa come “alloggi pubblici” o “locali”. Ma non sono sicuro di capire il tuo caso, e potresti essere alla guida di qualcosa di ancora più generale di quanto suggeriscano quei due nomi. Dopo tutto, hai incluso “eventi” nella tua lista, e un evento non è un tipo di luogo nella mia mente.

Ho indirizzato le altre persone alle letture su gen-spec e il modello relazionale nelle risposte precedenti.
Quando due tabelle sono molto simili, quando dovrebbero essere combinate?

Ma esita a mandarti nella stessa direzione, perché non mi è chiaro che tu voglia inventare un modello relazionale dei dati prima di build il tuo database. Un modello relazionale di un corpo di dati e un modello EAV degli stessi dati sono quasi totalmente in disaccordo l’uno con l’altro. Mi sembra che tu debba fare questa scelta prima ancora di esplorare come esprimere le specifiche di gen nel modello relazionale dei dati.

L’approccio “astratto” è meglio conosciuto come “Normalizzazione”, assomiglia al 3 ° modulo normale (3NF).

L’altro si chiama “Denormalized” e può essere un’opzione valida per le prestazioni … quando si incontrano problemi di velocità usando l’approccio Normalizzato, non prima.

Quando inizi a richiedere un gran numero di entity framework diverse (o anche prima …), una soluzione nosql sarebbe molto più semplice di entrambe le opzioni. Basta memorizzare ogni quadro / record con i campi esatti richiesti.

 { "id": 1, "type":"Restaurant", "name":"Messy Joe", "address":"1 Main St.", "tags":["asian","fusion","casual"] } 

Come avete le liste rappresentate nel codice? Direi che Listing sia un supertipo, con Shop , Restuarant , ecc. Come sottotipi?

Supponendo che, questo è un caso di come mappare sottotipi in un database relazionale. Ci sono generalmente tre scelte:

  • Opzione 1: tabella singola per sottotipo, con attributi comuni ripetuti in ogni tabella (nome, id, ecc.).
  • Opzione 2: tabella singola per tutti gli oggetti (il tuo approccio con una sola tabella)
  • Opzione 3: tabella per il supertipo e una per ogni sottotipo

Non esiste una soluzione universalmente corretta. La mia preferenza è generalmente quella di iniziare con l’opzione 3; fornisce una struttura intituitiva con cui lavorare, è abbastanza ben normalizzata e può essere facilmente estesa. Significa un singolo join per il recupero di ciascuna istanza, ma gli RDBMS sono ottimizzati per l’esecuzione di join in modo tale che in pratica non causino problemi di prestazioni.

L’opzione 2 può essere più performante per le query (senza join) ma causa problemi se altre tabelle devono fare riferimento a tutte le istanze di supertipo (proliferazione di chiavi esterne).

L’opzione 1 sembra a prima vista la più performante, anche se 2 caveat: (1) Non è resiliente al cambiamento. Se aggiungi un nuovo sottotipo (e quindi attributi diversi) dovrai modificare la struttura della tabella e migrarla. (2) Può essere meno efficiente di quanto sembri. Poiché la popolazione di tabelle è scarsa, alcuni DB non la memorizzano in modo particolarmente efficiente. Di conseguenza, può essere meno efficace dell’opzione 1, poiché il motore di query può eseguire join più rapidamente di quanto possa cercare spazi di tabella sparsi.

Quale scegliere dipende in realtà dal conoscere i dettagli del tuo problema. Suggerirei di leggere un po ‘sulle opzioni: questo articolo è un buon punto di partenza.

hth