Come progettare il database con il contenuto degli utenti

Se il numero di campi che non sono condivisi da tutti i diversi tipi di Location sono piccoli, vorrei solo andare per una singola tabella per tutte le Locations (incluse quelle personalizzate) e solo mettere un NULL valore nei campi che sono irrilevanti per quel tipo di posizione.

Se ci sono più di alcuni campi non condivisi, opterei per un approccio "chiave primaria condivisa". Qui hai una tabella per i campi comuni ( Location ) e tabelle aggiuntive con i campi per ogni specifico tipo di posizione ( SchoolLocation , ChurchLocation , CustomLocation , ecc.).
Il trucco è che le tabelle specifiche del tipo non hanno la propria chiave primaria, ma condividono la chiave primaria con la tabella Location .

Ad esempio, potresti avere queste tabelle e queste voci:

Location:
id |  Type  | Name
 1 | School | MIT
 2 | Custom | Home
 3 | Church | St. Pauls Cathedral

CustomLocation:
id | UserId
 2 | 12345

ChurchLocation:
id | Religion
 3 | Roman Catholic

Nota ogni tabella <Type>Location contiene solo voci per il tipo corrispondente e che i valori nella colonna id corrispondono alla voce corrispondente nella tabella Location .

Riguardo alle tue preoccupazioni per l'integrità dei dati, dovrebbe essere raro che tutti i dati non personalizzati vengano aggiornati allo stesso tempo, quindi è necessario progettare il processo di aggiornamento in modo tale che le modifiche a parte del database non corrompe le voci non correlate. E a meno che non stiate pensando di avere tabelle separate per ogni utente, lo stesso vale con l'eliminazione di voci personalizzate dopo che un utente chiude il suo account. Questa è una pratica standard quando si lavora con i database.

La tecnica che ho descritto per l'utilizzo di chiavi primarie condivise è anche una tecnica nota per modellare una relazione di ereditarietà tra le classi nel database.

È possibile gestire tutte le posizioni in una singola tabella e quindi spostare le colonne in altre tabelle che sono univoche per un gruppo di località (Sistema o Personalizzato). Creare una nuova tabella per ogni tipo può essere complicato e difficile da gestire. Questo ti dà anche la flessibilità man mano che questo sistema cresce. Il mio istinto a questo punto è che non ne avrai bisogno, ma mantieni il tuo atteggiamento positivo che avrai milioni di posizioni.

Sembra che tu stia facendo affidamento su alcuni dati strutturati per gestire problemi di sicurezza e integrità.

I'm worried about user data getting mixed up with system data during updates, or when users delete their data.

La tua app avrà una "vista" diversa o qualsiasi sia l'ambiente di sviluppo che le chiama, per le posizioni di sistema e le posizioni personalizzate / utente. Due tabelle diverse ti aiuteranno, ma dal momento che hai bisogno di più, una tabella funzionerà pure. Anche se neghi il permesso agli utenti di modificare la tabella dei percorsi di sistema, devi comunque assicurarti che ciascun utente possa solo modificare i loro record nella tabella personalizzata.

So, should we have a second, nearly identical CustomLocation table? But then how do we keep the Location IDs unique between the two tables? And we're increasing the code complexity of the application.

Potresti utilizzare i GUID per assicurarti che gli identificatori siano univoci o semplicemente iniziare una delle tabelle con un numero che puoi prevedere supererà l'altro.

Per quanto riguarda una grande tabella, sii più interessato alle colonne non utilizzate invece di troppe righe. La maggior parte dei RDBMS ha un sacco di strategie che utilizzano l'indicizzazione, i file e altre tecniche per migliorare le prestazioni con dataset di grandi dimensioni.