La mia prima domanda qui. Chiedetevi se è nel forum sbagliato ma questo sembra piuttosto concettuale. Sto cercando di fare qualcosa che vada contro la saggezza convenzionale e voglio ottenere un feedback sul fatto che sia del tutto folle o che si traduca in problemi, quindi critiche! Sono su PostgreSQL 9.1 ma potrebbe passare a 9.2 per questa parte di questo progetto.

Per ripetere: sembra ragionevole interrompere la 1NF in questo modo? Non sto cercando il debug del codice tanto quanto le persone vedono problemi che questo potrebbe portare.

Il problema

Nella contabilità a partita doppia, le transazioni finanziarie sono registrazioni a giornale con un numero arbitrario di righe. Ogni riga ha un valore a sinistra (debito) o un valore a destra (credito) che può essere modellato come valore singolo con valori negativi come debiti e positivi come crediti o viceversa. La somma di tutti i debiti e crediti deve essere uguale a zero (quindi se andiamo con un singolo campo importo, somma (importo) deve essere uguale a zero per ogni voce di giornale finanziario). I database basati su SQL, molto richiesti per questo tipo di lavoro, non hanno modo di esprimere questo tipo di vincoli in modo nativo e quindi qualsiasi approccio per imporlo nel database sembra piuttosto complesso.

Il modello di scrittura

Le voci del diario sono solo append. C'è una possibilità che aggiungeremo un modello di cancellazione ma sarà soggetto a un diverso insieme di restrizioni e quindi non è applicabile qui. Se e quando consentiamo le eliminazioni, probabilmente li faremo utilizzando una semplice designazione ON DELETE CASCADE sulla chiave esterna e richiediamo che le eliminazioni passino attraverso una stored procedure dedicata che possa far rispettare gli altri vincoli. Quindi inserimenti e selezioni devono essere sistemati, ma gli aggiornamenti e le eliminazioni non lo fanno per questo compito.

La mia soluzione proposta

La mia soluzione proposta è di rompere la prima forma normale e i vincoli del modello sugli array di tuple, con un trigger che spezza le righe in un'altra tabella.

CREATE TABLE journal_line (
    entry_id bigserial primary key,
    account_id int not null references account(id),
    journal_entry_id bigint not null, -- adding references later
    amount numeric not null
);

Quindi aggiungerei "metodi tabella" per estrarre debiti e crediti a fini di reporting:

CREATE OR REPLACE FUNCTION debits(journal_line) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$
SELECT CASE WHEN $1.amount < 0 THEN $1.amount * -1 ELSE NULL END; $$;

CREATE OR REPLACE FUNCTION credits(journal_line) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$
SELECT CASE WHEN $1.amount > 0 THEN $1.amount ELSE NULL END; $$;

Quindi la tabella di registrazione a giornale (semplificata per questo esempio):

CREATE TABLE journal_entry (
    entry_id bigserial primary key, -- no natural keys :-(
    journal_id int not null references journal(id),
    date_posted date not null,
    reference text not null,
    description text not null,
    journal_lines journal_line[] not null
);

Quindi un metodo di tabella e controlli i vincoli:

CREATE OR REPLACE FUNCTION running_total(journal_entry) returns numeric
language sql immutable as $$
SELECT sum(amount) FROM unnest($1.journal_lines); $$;

ALTER TABLE journal_entry 
ADD CONSTRAINT CHECK (((journal_entry.running_total) = 0));

ALTER TABLE journal_line
ADD FOREIGN KEY journal_entry_id REFERENCES journal_entry(entry_id);

E alla fine avremmo un trigger per il breakout:

CREATE OR REPLACE FUNCTION je_breakout() RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
 BEGIN
   IF TG_OP = 'INSERT' THEN
      INSERT INTO journal_line (journal_entry_id, account_id, amount)
      SELECT NEW.id, account_id, amount
        FROM unnest(NEW.journal_lines);
      RETURN NEW;
   ELSE
      RAISE EXCEPTION 'Operation Not Allowed';
   END IF;
 END;
$$; 

E infine

CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE ON journal_entry 
FOR EACH ROW EXECUTE_PROCEDURE je_breaout();

Ovviamente l'esempio sopra è semplificato. Ci sarà una tabella di stato che traccerà lo stato di approvazione consentendo la separazione dei compiti, ecc. Tuttavia l'obiettivo qui è quello di prevenire le transazioni sbilanciate.

Qualche feedback? Questo suona del tutto folle?

Soluzioni standard?

Per arrivare a questo punto devo dire che ho esaminato quattro diverse soluzioni ERP attuali per questo problema:

1. Rappresenta ogni elemento pubblicitario come debito e credito nei confronti di diversi account.
2. Uso di chiavi esterne contro la tabella degli elementi pubblicitari per imporre un totale parziale di 0
3. Uso dei trigger di vincoli in PostgreSQL
4. Forzare tutte le convalide qui solo attraverso la logica dell'app.

Le mie preoccupazioni sono che il primo è piuttosto limitante e molto difficile da controllare internamente. Non è programmatore trasparente e quindi mi sembra difficile lavorare in futuro. Il secondo mi sembra molto complesso e ha richiesto una serie di contrapposizioni e chiavi esterne contro se stessi per fare lavoro, e quindi mi sembra complesso, difficile da risolvere almeno nella mia mente, e quindi difficile da lavorare.

Il quarto potrebbe essere fatto in quanto costringiamo comunque tutti gli accessi tramite stored procedure e questa è la soluzione più comune (fare in modo che l'app completi le cose e genera un errore altrimenti). Tuttavia, penso che la dimostrazione che un vincolo sia seguito è superiore ai casi di test, e quindi la domanda diventa se questo di fatto genera anomalie di inserimento piuttosto che risolverle.

Se questo è un problema risolto, non è il caso che tutti siano d'accordo sulla soluzione ...