Qual è l'approccio migliore per la progettazione del database con molte colonne?

Se questo sito verrà utilizzato per la creazione di report ad hoc e prevedi un numero elevato di righe, dovresti progettare il database come Data Warehouse . Questo sposta l'attenzione dalla normalizzazione alle prestazioni e all'efficienza della query, che sembra appropriata per l'applicazione che hai descritto.

Per ulteriori informazioni a riguardo, consulta Modellazione dimensionale . Quelle tabelle con un numero elevato di colonne che rappresentano dati numerici sarebbero molto probabilmente Tabelle dei fatti e le tabelle più piccole e più descrittive sarebbero le dimensioni. Ulteriori informazioni su questo qui .

E nel caso in cui non hai notato il tema nei link che ho postato, Ralph Kimball ha pubblicato molti e molte buone informazioni sulla progettazione, l'implementazione e la manutenzione dei data warehouse. Leggi le sue cose!

Va bene avere molte colonne se ci sono veramente molti aspetti diversi di un'entità. Ma sales_annual_01, sales_annual_02 ecc. Urlano solo "cattiva progettazione". Se hai più versioni di essenzialmente lo stesso attributo, quasi certamente dovrebbero essere in una tabella separata che puoi unire alla tua tabella se necessario - in questo modo non devi aggiornare un numero sconosciuto di posizioni nella tua logica oncen i poteri che all'improvviso richieda rapporti di sette anni anziché rapporti di cinque anni (che conosci lo faranno, alla fine).

(Si noti che se si ha lo scenario che CFL_Jeff sospetta, questo è meno rilevante: la normalizzazione non è così importante quando non si modificano i dati o lo schema e le prestazioni sono le primo requisito.)

Prova questa divisione alle tabelle:

Companies
(
    CompanyPK PK,
    Name
)

Sales
(
    CompanyPK PK,
    Year PK,
    Value
)

Expenses
(
    CompanyPK PK,
    Year PK,
    Value
)

In questo modo puoi avere più anni per azienda e prendere in considerazione che alcune aziende potrebbero non essere esistite in un dato anno. Inoltre, non è necessario spostare i dati ogni anno, basta aggiungere righe.

L'utile sarebbe un calcolo (Vendite = Spese), quindi non hai bisogno di una tabella / colonne per quello.

Se hai molti campi diversi non calcolati, allora considera un approccio al dizionario, invece ...

Companies
(
    CompanyPK PK,
    Name
)

Fields
(
    FieldTypePK PK,
    CompanyPK PK,
    Year PK,
    Value
)

FieldTypes
(
    FieldTypePK PK,
    Name
)

Esempio di utilizzo per la seconda opzione:

select
    c.CompanyPK,
    c.Name
from Companies c
inner join Fields f1
on f1.CompanyPK = c.CompanyPK
inner join Fields f2
on f2.CompanyPK = c.CompanyPK
where f1.FieldPK = 1 and f1.Year = 2012 and f1.Value > 1000000
and f2.FieldPK = 2 and f2.Year = 2012 and f2.Value < 50000

1) What is the best database design approach? Is it ok to have these many number of columns?

2) How can it be normalized? (User can use any of these fields in search criteria).

Questo è solo un terribile progetto di database. La soluzione più ovvia è che devi averlo in terzo modulo normale :

Companies (
company_name,
...
)

FinancialResults (
period, 
sales_annual,
profit_annual,
...
)

Lo stile EAV l'approccio che puoi vedere in alcune risposte non è il modo più efficace e sarà un dolore nel didietro da cui estrarre i dati.

3) Is it ok to stick with MySQL, or modern document based databases like MongoDB should be better for it?

Sì, MySQL funzionerà perfettamente per quello. Non è il tipo, il volume o la velocità effettiva dei dati per i quali dovresti prendere in considerazione soluzioni no-SQL. E la soluzione no-SQL non funziona in modo efficiente se puoi eseguire una query su qualsiasi colonna.

Se ho capito bene, questi dati sono compilati regolarmente e utilizzati dall'utente finale come sorgente di dati di sola lettura ... Quindi il requisito fondamentale è di avere una struttura semplice da cui l'utente possa fare il rapporto.

Supponendo quanto sopra è vero. Stai facendo un buon lavoro dicendo che la normalizzazione non è importante, in questo caso darò all'utente un tavolo gigante per semplificarti la vita. (Puoi passare al n. 2)

Going NoSQL (MongoDB) renderà la vita degli utenti non tecnologici un vero inferno, con solo 5000 righe non c'è modo di avere problemi di prestazioni (è possibile superare # 3 e restare con MySQL)

Quindi ciò che rimane è il problema di troppe colonne. Onestamente non è l'approccio che vorrei prendere. ma non è nemmeno un grosso problema.

Vorrei rendere Anno enter code here una colonna quindi inserire una serie statica di colonne per sales_annual , profit_annual ect .. (Ma c'è una possibilità che al tuo utente non piacerà , quindi parlagli \ pugno.)

Per 5000 linee a 500 campi per riga, l'unico motivo per utilizzare un database relazionale è che tutti gli utenti sanno come utilizzare SQL per eseguire query e si pianifica di fornire loro SQL raw.

Nel momento in cui darai loro qualcosa di diverso da quello raw SQL per fare le loro ricerche, sei molto più bravo a buttar via il tuo DBMS e renderlo una semplice scansione sequenziale in un file di testo piatto. 5000 x 500 = 2,5e6, quindi hai 2,5 milioni di campi singoli. Supponendo una media di 10 byte per campo, si tratta di 25 milioni di byte. Questo è un array residente in memoria su un singolo PC e inizialmente la lettura del sucker in memoria è ciò che ti ucciderà. Supponendo una media di 100 byte per campo, sono ancora solo 250 milioni di byte. Forse lo fai, 50 milioni di byte in un colpo.

Solo perché hai i dati non significa che devi usare un sistema di gestione del database.

Passa a una struttura normalizzata. SQL è fondamentalmente integrato a questa scelta progettuale. Il mancato rispetto di ti causerà problemi. O i tuoi utenti comprenderanno veramente SQL, e non avranno problemi, o il tuo front end deve fare il lavoro pesante e mascherare il fatto che tutte queste colonne sono normalizzate.

In secondo luogo, usa gli UUID! Ciò impedirà una grande quantità di dolore nel collegamento dei tuoi dati disparati al volo. E i tuoi criteri di ricerca si adattano a ciò che l'utente chiede. Se vogliono tutti i record del 2010 per la società Foo, allora questo è solo un join interno della tabella dei record nella tabella della società in cui nome della società = Foo e un join interno nella tabella degli anni dove anno = 2010.

Infine, le tue prestazioni saranno molto veloci con una corretta configurazione, perché non stai infrangendo la progettazione SQL (vedi articolo 1). SQL è incentrato su set quindi impostare le cose per le query come nell'esempio 2 funzionerà molto velocemente. Se tutto è in un grande tavolo, all'improvviso il front-end deve attendere che ogni singola riga venga restituita su una rete , e quindi deve leggere < em> ogni singola riga e confrontarla con i criteri di ricerca. Questa non è programmazione procedurale, non fingere che sia.

Per un post eccellente su tutto questo, leggi questo articolo .