Effetto di denormalizzazione

Ecco uno scenario in cui mi chiedo se denormalizzare un database relazionale (MS SQL).

Descrizione testuale del requisito

Ho utenti (memorizzati in una tabella utenti).

Gli utenti appartengono agli account:

• Un account viene creato prima della creazione degli utenti
• Ogni utente deve appartenere esattamente a un account (né nessun account né più di un account)
• Qualsiasi account può contenere molti utenti
• L'assegnazione di un utente a un account è permanente: gli utenti non vengono mai spostati da un account all'altro

La tabella Users è predefinita, quindi non posso aggiungere una colonna "accountId" alla tabella Users; quindi per implementare account ho:

• Una tabella utenti, con una chiave userId
• Una tabella degli account, con un tasto accountId

• Una tabella UserAccounts, con colonne userId e accountId (per identificare l'account associato a ciascun utente); la tabella UserAccounts è vincolata:

  • Relazioni con le chiavi esterne alla tabella Utenti e account
  • Vincolo di unicità per garantire che un ID utente non venga visualizzato più di una volta

Poiché non riesco a modificare la tabella Utenti per inserire la colonna accountId, non posso garantire che ogni utente abbia un accountId. E infatti inserirò nel UserAccounts table dopo l'inserimento nella tabella Users, anche se probabilmente nella stessa transazione.

• Ho una dozzina di altre tabelle (ad esempio Vendite) che definiscono vari tipi di cose che appartengono agli utenti
• Queste tabelle hanno una colonna userId (per identificare a quale utente appartiene ogni riga)
• Il problema è che ho anche bisogno di sapere a quale account appartiene ogni cosa

Descrizione grafica del requisito

Ecco una versione riformulata dello stesso problema (grazie a MichaelT che ha suggerito questa riformulazione ).

   +-----------+    +--------------+      +--------------+
   | Users     |    | UserAccount  |      | Account      |
   +-----------+    +--------------+      +--------------+
+--> userId pk <----+ userId (uniq)|   +--> accountId pk |
|  |           |    | accountId    +---+  |              |
|  +-----------+    +--------------+      +--------------+
|                                                       
|                                                       
|  +-----------+                                        
|  | Sales     |                                        
|  +-----------+  (and several other tables like this)  
|  | saleId pk |                                        
+--+ userId fk |                                        
   |           |                                        
   +-----------+                                        

Non riesco a modificare la tabella Users per vari motivi: ecco perché l'account di ciascun utente ( singolo ) viene definito utilizzando la tabella UserAccount separata, invece di aggiungere una chiave ForeignId all'account Tabella utenti.

Questa tabella UserAccount implementa una relazione molti-a-uno tra utenti e account (non una relazione molti-a-molti). Potrebbe essere definito utilizzando i seguenti tasti:

• userId come chiave primaria
• (userId,accountId) come chiave primaria più userId come chiave univoca

La domanda

Ora per la domanda:

Quando si effettua una query su una tabella vendite che deve includere le informazioni sull'account, penso che ci siano due modi per implementare la conoscenza di quale account appartenga a ciascuna cosa:

1. Unisciti alla tabella nella tabella UserAccounts (per selezionare l'ID account per ogni ID utente):

   select S.*, UA.accountId
   from Sales S
       join UserAccount UA on S.userId = UA.userId
   where
       S.something = somethingElse
   

2. Denormalizza la tabella [s] vendite, memorizzando l'accountId in esso e l'ID utente (l'accountId può quindi essere recuperato da Vendite senza un join in AccountUtente):

   |  +--------------+                                        
   |  | Sales        |                                        
   |  +--------------+  (and several other tables like this)  
   |  | saleId pk    |                                        
   +--+ userId fk    |                                        
      | accountId fk |                                        
      +--------------+                                        
   

   Se avessi fatto il secondo avrei potuto garantire l'integrità definendo userId plus accountId pair / combination come un vincolo di chiave esterna nella tabella UserAccounts (per garantire che l'accoppiamento di accountId con userId corrisponda all'accoppiamento definito nella tabella UserAccounts).

   CONSTRAINT Sales_FK FOREIGN KEY (userId,accountId)
       REFERENCES UserAccounts (userId,accountId)
   

Non ho una vasta esperienza con il design del database: l'ho imparato dai libri (di Joe Celko).

Sono inibito contro denormalizzazione in generale, ma la denormalizzazione sembra appropriata qui: perché un JOIN extra in ogni istruzione SELECT (per ottenere l'ID account), quando lo stesso potrebbe essere ottenuto memorizzando l'accountId nella tabella con un (composto o composito) chiave esterna.

C'è una ragione convincente per usare (o non usare) una di queste due possibilità?

In caso contrario, sono tentato di utilizzare il 2o metodo:

• perché è più semplice definire l'istruzione select (senza un join)
• perché è (presumibilmente) prestazioni leggermente migliori (senza un join)
• perché garantisce che una riga corrispondente (con un accountId) esista nella tabella UserAccounts