mysql, memorizza un singolo pezzo di dati per riga

In base alle informazioni fornite:

The database will be used to drive a custom CMS system which is intended to store a interconnected system of data.

'Interconnected' implica relazionale, motivo per cui dovresti usare un design di database relazionale tradizionale.

It will involve customer data, location data and job data.

Esistono già almeno tre tabelle: tabella dei clienti, tabella delle posizioni con una chiave esterna per il cliente e tabella dei lavori con una chiave esterna ad un'altra tabella correlata: utente? dipendente?

We want to be able to track individual database changes based on time so that we can maintain an on-site backup (the system will be cloud based) which we can use in the event that we lose internet connectivity.

I backup ai fini del disaster recovery dovrebbero probabilmente essere gestiti a un "livello" (infrastruttura) diverso rispetto alla progettazione del database. Nondimeno, non penso ancora che un database verticale sia la soluzione migliore per questo: potresti utilizzare un sistema di registrazione separato tramite tabelle aggiuntive o scrivere su un file se vuoi utilizzare un approccio del genere. Ma penso che includere i parametri created_by, timestamp, start_date, end_date, ecc. Come metadati nelle tabelle di dati sia l'approccio migliore per conservare la cronologia.

The system will be strongly permission based, only allowing people to see/change information that they are authorized to see.

Suggerirei di costruire o utilizzare un sistema di autenticazione pre-esistente basato sui ruoli per questo. È possibile ottenere la sicurezza a livello di tabella o di riga archiviando i ruoli degli individui che dovrebbero essere in grado di leggere / modificare i dati insieme ai dati o in tabelle utente / ruolo separate. Come lo avresti impostato è probabilmente una discussione molto più profonda di quella che potremmo avere qui.

Most of the data will be viewed in a timeline format, based on the time that the info is entered into the system.

ORDER BY timestamp :)

Consiglio vivamente di esaminare i sistemi CMS esistenti prima di arrivare troppo lontano alla pianificazione su uno personalizzato. Molti di loro hanno molte delle funzionalità che hai citato.

Questo approccio è talvolta usato in situazioni insolite, come avere tabelle che avrebbero un numero enorme di colonne ciascuna scarsamente popolate, o con dati definiti dall'utente in cui le colonne non sono note fino a quando l'applicazione è in esecuzione.

Il motivo per cui non viene usato di più è che non è come i database di relazioni come MySQL sono progettati per funzionare, e quindi farlo è problematico. Il modo in cui gli indici funzionano è basato su colonne, e se la tua colonna tiene davvero un po 'di tutto allora dovrai indicizzare tutto o niente, e Query Optimizer avrà difficoltà a determinare quando sarà utile. Stai privando il database dei metadati accurati che altrimenti avrebbe, e questo ti costerà nel rendimento.

Inoltre, l'SQL che scrivi sarà più difficile. Se vuoi ottenere una serie di dati correlati, dì tutto da una pagina web, dovrai raggruppare per ... qualcosa che hai inserito nel database per consentire di raggruppare. Molto probabilmente finirai per avere un tavolo da gioco, una tabella delle righe, una tabella di colonne e una tabella di valori, e raggruppare per la tabella delle righe. Questo è un dolore.

Per quanto riguarda i tuoi obiettivi dichiarati di eseguire facilmente il backup incrementale, cerca su singole righe e riesamina in base al suo stato in una determinata data, non aiuta il backup, non rende più facile cerca su un singolo valore (e rende più difficile la ricerca di più valori in una riga logica), e nella migliore delle ipotesi non fa nulla per aiutarti a rivedere il suo stato in una certa data (il timestamp è la chiave per farlo).

Quindi, in base alle informazioni fornite, non penso che tu sia un buon candidato per l'utilizzo di questo design. Se è così, dovresti considerarti fortunato - è un dolore.