Progettazione di un database per una tabella con un numero enorme di righe

Potresti usare sqlite . Può memorizzare un sacco di righe e funzionare su molti sistemi operativi (Windows, Linux, Android, MacOSX).

Potresti prendere in considerazione l'installazione e l'uso di alcuni sistemi Linux su quel singolo PC e sviluppare il tuo sistema su questo (forse come un'applicazione web utilizzando un database) e utilizzare alcuni software RDBMS gratuiti come PostGreSQL o MariaDb (o MySQL , molto vicino a MariaDb). Sono in grado di gestire molte righe (vedi questo per PostGreSQL e questo e altre cose per MySQL). In pratica, i limiti sono limitati dalle capacità hardware.

I have decided to develop an MS Access file-based desktop application.

Potrebbe non essere stata la decisione migliore. Dovresti considerare alternative al software libero (come quelle sopra menzionate) e potresti pensare ad alcune applicazioni web (utilizzabili da diversi browser, magari su tablet economici).

Si noti che RDBMS di centinaia di milioni di righe viene regolarmente distribuito su sistemi Linux che eseguono PostGreSQL o MariaDb (o MySQL, quasi equivalente).

Indipendentemente dalla soluzione tecnica che pensi, non dimenticare di eseguire il backup dei dati molto periodicamente e di definire alcune procedure di backup (e di controllare ogni tanto che puoi ripristinare dai backup).

La maggior parte del costo è probabilmente correlata a il tempo e gli sforzi di sviluppo e alle tue capacità. Questo è probabilmente più costoso dell'hardware o di qualsiasi licenza software di cui avrai bisogno.

If I can anticipate correctly, this design is destined to fail.

Questo è falso se si utilizza un RDBMS reale su un sistema Linux (quelli disponibili gratuitamente sulla maggior parte delle distribuzioni Linux ), oppure se usi sqlite . Il tuo design è valido (e potresti usare software gratuito per questo, tutti i prodotti menzionati qui sono software libero). La scelta del database e del sistema operativo è discutibile. BTW, sviluppare da zero il tuo POS potrebbe essere più costoso rispetto all'utilizzo di soluzioni esistenti (e potresti persino trovare, adattare e migliorare alcuni quelli software gratuiti ) .

For instance, after only one week, DailySales table will become so huge that the database

Ogni giorno 10000 file in più sono minuscole . La maggior parte degli RDBMS (e sqlite) può gestirli. In 3 anni, ciò significa 10 milioni di righe, non un grosso problema. Ovviamente è necessario dimensionare correttamente il disco (ma assumendo 4Kbyte di spazio su disco per riga, 40Gbytes non è molto, probabilmente nel tuo caso ogni riga consuma solo diverse dozzine di byte). Ma il tuo database è piccolo o minuscolo w.r.t. alla pratica di oggi. Non preoccuparti del numero di righe (ma definisci correttamente rilevanti indici di database , sono legati alle domande che farai). La maggior parte dei database può gestire facilmente molte dozzine di milioni di righe (se il tuo schema del database è abbastanza buono), questo non è un problema oggi. Quindi non hai un "numero enorme di righe" ma piuttosto piccolo.

Se (per una ragione che non hai spiegato) hai bisogno di sviluppare un software desktop (non qualcosa in esecuzione in un browser) potresti sviluppare alcune applicazioni desktop con una GUI su Linux usando alcuni RDBMS (ad esempio usando Qt ). Tuttavia, un'applicazione web può essere utilizzata da diversi tablet economici. E puoi trovare librerie server HTTP (ad es. Wt o libonion , per C o C ++ su Linux) per svilupparlo (vedi anche questo ).

after only one week, DailySales table will become so huge that the database will be cumbersome to manage

Ne dubito. Utilizzo regolarmente MS Access da oltre 20 anni e l'ho utilizzato con successo per database con oltre 500.000 fino a 1 milione di record, purché ci fossero solo pochi utenti concorrenti. Tuttavia, è necessario occuparsi di cose simili come quando si utilizzano sistemi più grandi:

• Separazione fisica al 100% tra il back-end e il frontend del database (che può essere facilmente perso nel caso in cui si stiano sviluppando backend e frontend con MS Access)

• normalizzazione e indicizzazione corrette

• corretta strategia di aggiornamento per le nuove versioni dell'applicazione & lo schema DB

• implementazione di backup & strategia di recupero, forse un database di riparazione e amp; strategia di compattazione

• strategia di archiviazione a lungo termine

Dato il numero atteso di record a settimana, suppongo che dovresti mirare a una strategia in cui archivi i record di vendite più vecchi di volta in volta quando sono più vecchi di (circa) sei mesi.

A seconda dei requisiti del tuo cliente e dei casi d'uso relativi alle vendite precedenti, questo potrebbe significare

• per archiviare l'intero file di database e quindi eliminare tutte le vendite precedenti dal "database attivo" corrente e / o

• per aggregare le vendite più vecchie in modo efficiente dal punto di vista dello spazio, quindi è possibile fornire tali dati online per un periodo molto più lungo di soli 6 mesi. Forse le "vendite totali al giorno" sono sufficienti dopo 6 mesi?

• per consentire agli utenti di passare da file di database meno recenti per accedere alle vendite meno recenti (probabilmente in modalità "di sola lettura") e ai record di file di database più recenti per le vendite più recenti.

Se un intervallo di sei mesi è troppo frequente per il cliente (spesso sono necessari 12 mesi per motivi fiscali), è possibile scegliere un diverso sistema db senza server che supporta più record / file più grandi di 2 GB (come SQLite o MS SQL Server Local ). O semplicemente prova MS Access e guarda fino a che punto arriva il tuo, non mi stupirei se un intero anno di dati possa essere gestito correttamente nel tuo caso. Assicurati di implementare una strategia di archiviazione che può essere applicata quando è necessario.

I limiti

I volumi non dovrebbero essere un problema per un database moderno.

MS-Access ha un vincolo di massimo circa 2 GB per tabella. Osservando la dimensione del tipo , sembra che un record di DailySales sia attualmente di circa 24 byte. Andiamo a 40. Ciò significa che MSAccess sarebbe ancora in grado di archiviare 50 milioni di record, ovvero 64 anni di dati di vendita se il tuo negozio produce in media 15.000 righe a settimana.

Un vincolo più concreto potrebbe essere il tipo del campo ID. Se scegli la numerazione automatica, che è un intero senza segno a 4 byte, sarai limitato a 4 milioni di record, un limite che potrebbe essere raggiunto entro 5 anni già . Una soluzione alternativa potrebbe essere quella di utilizzare una chiave primaria composta, con l'anno commerciale e il numero autonumero e reimpostare il numero autonum ogni anno.

La performance

Potresti essere più preoccupato nelle prestazioni. Ciò che è importante lì, è indicizzare le tabelle almeno sui loro campi ID (per accelerare i join). Indica anche la data in DailySales (per accelerare l'ordinamento).

Solo a scopo illustrativo, l'indicizzazione consente al database di trovare qualsiasi record in 10 anni di dati di vendita in meno di 15 letture, anziché passare attraverso 7 milioni di record.

Il più grande impatto sulle prestazioni con MSAccess è l'accesso multiutente, poiché ogni PC eseguirà un motore MSAccess che dovrà accedere al file da solo, mentre su un DBMS si avrà un processo server dedicato . Tuttavia, nel tuo caso d'uso, hai solo un PC, quindi questa non dovrebbe essere la tua preoccupazione principale.

Il design

Senza conoscere gli obiettivi, è difficile giudicare il design. Ma da quello che posso vedere:

• Le statistiche di vendita quantitative sui prodotti saranno facili, presupponendo che il tipo di unità di prodotto non cambi mai.
• I dati di vendita saranno difficili da calcolare perché non esiste un facile join tra ProductPriceHistory dove il prezzo è memorizzato e DailySales che contiene le quantità da moltiplicare con il prezzo unitario. Faresti meglio a memorizzare un ProductPriceHistoryID nel DailySales.
• Suggerirei persino di memorizzare il prezzo utilizzato in DailySales , poiché ciò potrebbe consentire di registrare sconti ad-hoc, in caso di contrattazione dei clienti o piccoli problemi su una casella di prodotto specifica.

Conclusione

Se nonostante i tuoi argomenti, il proprietario non vuole investire in un DBMS, puoi certamente iniziare in piccolo con MSAccess. Se dopo i primi anni, la performance diminuirà significativamente, nonostante gli indici e altre ottimizzazioni , quindi potresti passare a un sistema più robusto.

Se vuoi supportare query ad-hoc, usa solo SQLLite, Sql Express o un altro motore db "locale".

Se non ti aspetti di dover eseguire query ad-hoc, ma supporta una serie fissa di rapporti, puoi esaminare l'approccio delle "visioni materializzate". Questo è un approccio in cui si guarda la vista come osservabile su osservabili sottostanti. Il flusso di eventi di immissione dei dati è il tuo osservabile principale, che viene spinto verso il basso in una pipeline di elaborazione per aprirsi a ventaglio in ogni vista materializzata. In altre parole. Il modo in cui memorizzi quelle viste e recuperale è quindi un problema molto più banale.