Come si progetterà un database utente con campi personalizzati

Considerare questo come un'alternativa. I due esempi precedenti richiedono entrambi di apportare modifiche allo schema man mano che l'ambito dell'applicazione cresce, inoltre la soluzione "custom_column" è difficile da estendere e mantenere. Alla fine finirai con Custom_510 e poi immaginerai quanto sarà orribile questo tavolo con cui lavorare.

In primo luogo, utilizziamo lo schema delle aziende.

[Companies] ComnpanyId, COMPANY_NAME, CREATED_ON

Successivamente utilizzeremo lo schema degli utenti anche per gli attributi richiesti di livello superiore che verranno utilizzati / condivisi da tutte le società.

[Users] UserId, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CREATED_ON

Poi costruiamo una tabella in cui definiremo i nostri attributi dinamici che sono specifici per gli attributi utente personalizzati di ciascuna azienda. Quindi qui un valore di esempio della colonna Attributo sarebbe "LikeMusic":

[UserAttributeDefinition] UserAttributeDefinitionId, CompanyId, Attribute

Successivamente definiamo una tabella UserAttributes che conserverà i valori degli attributi utente

[UserAttributes] UserAttributeDefinitionId, UserId, Value

Questo può essere modificato in molti modi per migliorare le prestazioni. È possibile utilizzare più tabelle per UserAttributes rendendo ognuno specifico per il tipo di dati memorizzato in Value o semplicemente lasciandolo come VarChar e utilizzandolo come archivio di valori chiave.

Potresti anche voler spostare CompanyId fuori dalla tabella UserAttributeDefiniton e in una tabella di riferimenti incrociati per le prove future.

Utilizza un database NoSQL. Ci sarebbero documenti dell'azienda e dell'utente. Gli utenti avrebbero creato parte del loro schema in modo dinamico sulla base di un modello utente (testo per indicare campi / tipi per quella società.

\Company\<uniqueidentifier>
    - Name: <Name>
    - CreatedOn: <datetime>
    - UserTemplate: <Text>

\User\<uniqueidentifier>
    - COMPANY_ID: <ID>
    - FIRST_NAME: <Text>
    - LAST_NAME: <Text>
    - EMAIL: <Text>
    - CREATED_ON: <datetime>
    - * Dynamically created fields per company

Ecco come potrebbe apparire in qualcosa come Firebase.com Dovresti imparare come farlo in qualunque cosa tu scelga.

Se ti capita spesso di imbatterti in richieste di campi personalizzati, lo modellerei in modo abbastanza simile al database. Creare una tabella che contenga i metadati relativi a ciascun campo personalizzato, CompanyCustomField (a cui appartiene, tipo di dati, ecc.) E un'altra tabella CompanyCustomFieldValues che contiene CustomerId, FieldId e il valore. Se stai usando qualcosa come Microsoft Sql Server, vorrei che la colonna valore fosse un tipo di dati sql_variant.

Ovviamente non è facile in quanto avrete bisogno di un'interfaccia che consenta agli amministratori di definire campi personalizzati per ogni cliente e un'altra interfaccia che utilizza effettivamente questi metadati per costruire un'interfaccia utente per raccogliere i valori dei campi. E se hai altri requisiti, come il raggruppamento dei campi insieme o la necessità di eseguire un campo di selezione, dovrai farlo con più metadati / altre tabelle (ad es. CompanyCustomFieldPickListOptions).

Questo non è banale, ma ha il vantaggio di non richiedere modifiche al database / modifiche al codice per ogni nuovo campo personalizzato. Altre caratteristiche dei campi personalizzati dovranno essere codificate (ad esempio, se si desidera regex validare un valore stringa, o solo consentire date tra determinati intervalli, o se è necessario abilitare un campo personalizzato basato su un altro valore campo personalizzato ).

Un'alternativa alle altre risposte è avere una tabella chiamata profile_attrib, o simile che lo schema sia completamente gestito dalla tua applicazione.

Man mano che vengono aggiunti gli attributi personalizzati, ALTER TABLE profile_attrib ADD COLUMN like_movie TINYINT(1) , puoi proibire di eliminarli. Ciò ridurrebbe al minimo il tuo join, pur garantendo flessibilità.

Immagino che il compromesso dei bit sia che l'applicazione ora ha bisogno di alterare i privilegi della tabella nel database, e devi essere intelligente nel sanificare i nomi delle colonne.

La tua domanda ha molte potenziali soluzioni. Una soluzione è archiviare gli attribrutes aggiuntivi come XML. L'XML può essere memorizzato come testo o se si utilizza un database che supporta i tipi XML come XML (SQL Server). La memorizzazione come testo limita la tua capacità di interrogazione (come la ricerca su un attributo personalizzato), ma se la memorizzazione e il recupero sono tutte le tue necessità, allora è una buona soluzione. Se è necessario eseguire una query, archiviare l'XML come tipo XML potrebbe essere un'opzione migliore (sebbene sia più specifica del fornitore).

Ciò consentirà di archiviare qualsiasi numero di attributi per un cliente semplicemente aggiungendo una colonna di addizione nella tabella dei clienti. Uno potrebbe memorizzare gli attributi come un hashset o un dizionario, uno perderà la sicurezza del tipo poiché tutto sarà una stringa con cui iniziare, ma se si impone una stringa di formato standard per date, numeri, booleani funzionerà OK.

Per ulteriori informazioni:

link

Anche la risposta di WalterMitty è valida, anche se se si hanno molti clienti con attributi diversi si potrebbe finire con molte tabelle se si segue il modello di ereditarietà. Dipende dal numero di attributi personalizzati condivisi tra i clienti.

Dovresti normalizzare il tuo database in modo da avere 3 diverse tabelle per ogni diverso tipo di profilo aziendale. Usando il tuo esempio, avresti tabelle con colonne:

USER_ID, LIKE_MOVIE, LIKE_MUSIC

USER_ID, FAVORITE_CUISINE

USER_ID, OWN_DOG, DOG_COUNT

Questo approccio presuppone che tu conosca la forma delle informazioni che un'azienda vuole immagazzinare in anticipo e che non cambierà spesso. Se la forma dei dati è sconosciuta in fase di progettazione, sarebbe probabilmente meglio andare con quel campo JSON o con un database nosql.

Per una ragione o per l'altra, i database sono l'unico campo in cui l'effetto della piattaforma interna si presenta più spesso. Questo è solo un altro caso di comparsa del pattern anti.

In questo caso, stai cercando di combattere la soluzione naturale e corretta. Gli utenti della società A non sono utenti della società B e dovrebbero avere le proprie tabelle per i propri campi.

Il tuo fornitore di database non ti addebita dalla tabella e non è necessario il doppio dello spazio su disco per il doppio delle tabelle (infatti, avere due tabelle è più efficiente perché non si memorizzano gli attributi di A per gli utenti di B. la memorizzazione di soli NULL richiede spazio).

Naturalmente, se esistono campi comuni sufficienti, è possibile suddividerli in una tabella utenti condivisa e disporre di una chiave esterna in ciascuna tabella utente specifica dell'azienda. Questa è una struttura così semplice che nessun ottimizzatore di query del database si trova in difficoltà. Qualsiasi JOIN necessario è banale.

La mia soluzione presuppone che chiamerai questa query da un programma e dovresti essere in grado di eseguire la post-elaborazione. Puoi avere le seguenti colonne:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CUSTOM_VALUES

CUSTOM_VALUES sarà di tipo stringa che memorizza coppia di chiavi e valori. la chiave sarà il nome della colonna e il valore sarà il valore della colonna, ad es.

LIKE_MOVIE;yes;LIKE_MUSIC;no;FAV_CUISINE;rice

in questo CUSTOM_VALUES salvi solo le informazioni che esistono. Quando esegui una query dal programma, puoi suddividere questa stringa e utilizzarla.

Ho usato questa logica e funziona bene, è solo che dovrai applicare la logica di filtraggio nel codice e non nella query.