Ho un problema di progettazione del database che non sono abbastanza sicuro di come affrontare, né se i benefici fuori pesano i costi. Spero che alcuni membri di P.SE possano dare un feedback sul mio progetto suggerito, così come su esperienze simili che potrebbero essersi imbattute.

Come funziona, sto costruendo un'applicazione che ha grandi richieste di reporting. La velocità è un problema importante, poiché ci saranno impieghi di punta durante tutto l'anno.

Questa applicazione / database ha una relazione multipla, molti-a-molti.

es

object a
object b
object c
object d

object b has relationship to object a
object c has relationship to object b, a
object d has relationship to object c, b, a

In teoria, questo potrebbe andare avanti per livelli illimitati, anche se la logica impone che potrebbe solo andare così lontano.

La mia idea qui, per accelerare i rapporti, sarebbe quella di creare una tabella syndicate che funge da tabella di join molti-a-molti globale. In questa tabella (con l'esempio fornito), si potrebbe vedere:

+----------+-----------+---------+
| child_id | parent_id | type_id |
+----------+-----------+---------+
|    b     |     a     |    1    |
|    c     |     b     |    2    |
|    c     |     a     |    3    |
|    d     |     c     |    4    |
|    d     |     b     |    5    |
|    d     |     a     |    6    |
+----------+-----------+---------+

Dove a, b, c e d si tradurrebbero nei rispettivi ID nelle rispettive tabelle. Quindi, per semplificare la segnalazione di tutto ciò che esiste sull'oggetto d, è possibile eseguire una query

SELECT * FROM 'syndicates' ... JOINS TO child and parent tables ... WHERE parent_id=a and type_id=6;

piuttosto che avere una query con un join per ogni livello della catena.

Il problema

Questa tabella cresce esponenzialmente e, in un dato anno, potrebbe facilmente superare 20.000 record per un cliente. Dati molti client in più anni, questa tabella esploderà MOLTO rapidamente a milioni di record e oltre.

Ora, nel tempo, il database verrà partizionato su più server, ma mi piacerebbe (come la maggior parte) mantenere il numero di server il più basso possibile pur continuando a offrire flessibilità.

Anche le scritture e gli aggiornamenti sarebbero esponenzialmente più lunghi (anche se probabilmente non visibili all'utente finale) poiché ci sarebbero più inserimenti / aggiornamenti / scansioni su questa tabella per tenerlo sincronizzato.

Sto andando nella direzione giusta qui, o sono fuori strada. Cosa faresti in una situazione simile? Questa soluzione sembra eccessivamente complessa, ma consente la massima flessibilità e le operazioni di lettura più veloci.

Sidenote 1 : questa struttura mi consente di aggiungere facilmente nuovi livelli all'albero.

Sidenote 2 - L'interrogazione del database per questo database avviene tramite un framework ORM.