Vincoli del database per una struttura di cartelle ricorsive

4

In un'applicazione, ho una struttura di cartelle ricorsiva (come le cartelle in OS X o nel file system di Windows).

Ogni cartella può contenere tre tipi di cose:

  • Altre cartelle (da cui la struttura ricorsiva)
  • I dipendenti
  • Attività

Ecco i modelli semplificati per questi elementi:

class Folder:
    parent_folder: Folder

class Employee:
    folder: Folder
    task: Task

class Task:
    folder: Folder
    name: StringField()

Come puoi capire dai modelli precedenti, a ogni Employee può essere attribuito un singolo Task .

Il punto dolente è che un dipendente può avere solo un'attività che si trova nell'albero delle cartelle di questo dipendente ( alias parte del suo percorso ). Lasciatemi illustrare questo con alcuni esempi:

Valid:
    Employee in /MyFolder/OtherFolder
    Task in /MyFolder

Valid:
    Employee in /MyFolder/OtherFolder
    Task in /MyFolder/OtherFolder

Invalid:
    Employee in /MyFolder
    Task in /MyFolder/OtherFolder # not in Employee’s path

Invalid:
    Employee in /MyFolder
    Task in /RandomFolder # not in Employee’s path

Comportamento desiderato

Se un dipendente o un'attività viene spostato in modo che l'attività di un dipendente diventa inaccessibile per detto dipendente, mi piacerebbe avere la relazione tra i due% dinullified, in modo che l'impiegato diventi privo di attività.

Se, in qualsiasi momento, nel database, un Dipendente ha un'attività per cui non ha accesso (non è nel suo percorso), il database dovrebbe essere considerato danneggiato.

Domande

È possibile implementare questo comportamento a livello di database, in modo che funzioni come i vincoli di chiave esterna con l'annullamento delle regole a cascata, ad esempio?

Se non è possibile a livello di database, puoi pensare a un modo per implementarlo a livello di applicazione e mantenere l'integrità del database in qualsiasi momento?

Domanda sussidiaria: dovremmo riconsiderare la nostra struttura dati e implementarne una che sarebbe più adatta a questo particolare problema?

Ciò che abbiamo provato finora (e i motivi per cui non ha funzionato):

  • Implementa questo comportamento a livello di applicazione - non funziona perché non possiamo garantire l'integrità del database in questo caso
  • Utilizza viste o Common Table Expressions (CTE) - non funziona perché le chiavi esterne non possono essere utilizzate all'interno di Views o CTE
  • Utilizza una tabella separata per le relazioni - non funziona a causa dell'aspetto ricorsivo della struttura che ci impedisce di utilizzare i vincoli FK per questo obiettivo

Una nota sullo stack: PostgreSQL - SQLAlchemy

Per mantenere questa domanda (relativamente) breve, ho semplificato il problema e omesso un certo numero di elementi - chiedi se hai bisogno di ulteriori dettagli

Modifica: avrei dovuto dire che il recupero della cartella di un oggetto da puro SQL è in realtà molto meno banale che nel mio esempio

    
posta Jivan 30.09.2015 - 16:39
fonte

2 risposte

5

Se il database è sotto controllo dell'applicazione (non modificato da altro), fallo a livello di applicazione, non a livello di database.

In teoria potresti farlo con i grilletti, ma non penso che dovresti. Il controllo della validità di una tale struttura richiede query computazionalmente complesse e vi è il pericolo significativo che ciò abbia un impatto negativo sulle prestazioni e la scalabilità. È una possibilità, ma questo non è un buon design da scegliere senza una chiara necessità (e non ne vedo uno dato l'informazione nella domanda).

Non capisco la preoccupazione "non possiamo garantire l'integrità del database". Perchè no? Se il database può essere modificato solo tramite l'applicazione e si garantisce (e si esegue un test completo) che ogni azione eseguita dall'applicazione sia eseguita correttamente e non provochi uno stato non valido, non si dovrebbe avere un problema con l'integrità del database. E questo tipo di test è ciò di cui hai bisogno comunque per assicurarti che il tuo programma sia robusto.

Se hai bisogno di ulteriore garanzia per qualche motivo (molto probabilmente perché il database può essere modificato all'esterno dell'applicazione), potresti prendere in considerazione i trigger. Tuttavia, se non è performante, è possibile creare un codice personalizzato che esegue un controllo di integrità su richiesta. Oppure potresti prendere in considerazione la possibilità di fornire un'API per gli altri che hanno bisogno di modificare il database, piuttosto che consentire la modifica diretta del database

    
risposta data 30.09.2015 - 17:01
fonte
2

Questo è perfettamente possibile con i trigger e se è necessario garantire l'integrità a un livello indipendente dall'applicazione, questa è probabilmente la soluzione migliore. Da questo esempio puoi vedere che la ricorsione è possibile nelle stored procedure POSTGRESQL, quindi attraversare l'albero non dovrebbe essere troppo difficile da implementare.

Naturalmente, potresti dover implementare i trigger "AFTER INSERT", "AFTER UPDATE" e "AFTER DELETE" per le tabelle Employee e Task , e per rendere questa prova a prova di proiettile senza un notevole impatto sulle prestazioni devi essere attento, ma per la mia esperienza, questi sono problemi risolvibili.

    
risposta data 30.09.2015 - 17:12
fonte

Leggi altre domande sui tag