Jak zaprojektowałbyś bazę danych użytkowników z niestandardowymi polami

To pytanie dotyczy tego, jak powinienem zaprojektować bazę danych, mogą to być relacyjne / nosql bazy danych, w zależności od tego, co będzie lepszym rozwiązaniem

Biorąc pod uwagę wymóg, w którym będziesz musiał stworzyć system, który będzie obejmował bazę danych do śledzenia „Firmy” i „Użytkownika”. Jeden użytkownik zawsze należy tylko do jednej firmy

• Użytkownik może należeć tylko do jednej firmy
• Firma może mieć wielu użytkowników

Konstrukcja stołu „Firma” jest dość prosta. Firma będzie miała następujące atrybuty / kolumny: (bądźmy prostsze)

ID, COMPANY_NAME, CREATED_ON

Pierwszy scenariusz

Prosty i bezpośredni, wszyscy użytkownicy mają ten sam atrybut, więc można to łatwo zrobić w stylu relacyjnym, tabela użytkowników:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CREATED_ON

Drugi scenariusz

Co się stanie, jeśli różne firmy chcą przechowywać różne atrybuty profilu dla swojego użytkownika. Każda firma będzie miała zdefiniowany zestaw atrybutów, które miałyby zastosowanie do wszystkich użytkowników tej firmy.

Na przykład:

• Firma A chce przechowywać: LIKE_MOVIE (boolean), LIKE_MUSIC (boolean)
• Firma B chce przechowywać: FAV_CUISINE (ciąg)
• Firma C chce przechowywać: OWN_DOG (boolean), DOG_COUNT (int)

Podejście 1

brutalną siłą jest posiadanie jednego schematu dla użytkownika i pozwalanie mu mieć wartości zerowe, gdy nie należą do firmy:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, LIKE_MOVIE, LIKE_MUSIC, FAV_CUISINE, OWN_DOG, DOG_COUNT, CREATED_ON

Co jest dość nieprzyjemne, ponieważ skończysz z dużą liczbą NULLS i wierszy użytkowników, które mają dla nich kolumny, które nie są dla nich istotne (tj. Wszyscy użytkownicy należący do Firmy A mają wartości NULL dla FAV_CUISINE, OWN_DOG, DOG_COUNT)

Podejście 2

drugim podejściem jest posiadanie „pola swobodnego”:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CUSTOM_1, CUSTOM_2, CUSTOM_3, CREATED_ON

Co samo w sobie byłoby paskudne, ponieważ nie masz pojęcia, jakie są pola niestandardowe, typ danych nie będzie odzwierciedlał przechowywanych wartości (np. Zapiszemy wartość int jako VARCHAR).

Podejście 3

Zajrzałem do pola JSON PostgreSQL, w którym to przypadku będziesz mieć:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CUSTOM_PROFILE_JSON, CREATED_ON

W takim przypadku, w jaki sposób można zastosować różne schematy dla użytkownika? Użytkownik z firmą A będzie miał schemat, który wygląda

 {"LIKE_MOVIE":"boolean", "LIKE_MUSIC": "boolean"}

Podczas gdy użytkownik z firmą C będzie miał inny schemat:

 {"OWN_DOG ":"boolean", "DOG_COUNT": "int"}

Jak mam rozwiązać ten problem? Jak właściwie zaprojektować bazę danych, aby umożliwić stosowanie tego elastycznego schematu dla pojedynczego „obiektu” (użytkownika) na podstawie relacji, którą mają (firma)?

rozwiązanie relacyjne? rozwiązanie nosql?

Edycja: Pomyślałem także o tabeli „CUSTOM_PROFILE”, która zasadniczo przechowuje atrybuty użytkownika w wierszach, a nie w kolumnach.

Z tym podejściem wiążą się 2 problemy:

1) Dane rosną w przeliczeniu na użytkownika, rosną jako wiersze, a nie kolumny - a to oznacza, aby uzyskać pełny obraz użytkownika, trzeba wykonać wiele połączeń, wiele połączeń do tabeli „profil niestandardowy” na różnych atrybutach niestandardowych

2) Wartość danych jest zawsze przechowywana jako VARCHAR, aby była generyczna, nawet jeśli wiemy, że dane powinny być liczbami całkowitymi lub logicznymi itp.

Proszę rozważyć to jako alternatywę. Dwa poprzednie przykłady wymagają wprowadzenia zmian w schemacie wraz ze wzrostem zakresu aplikacji, a ponadto rozwiązanie „custom_column” jest trudne do rozszerzenia i utrzymania. W końcu skończysz z Custom_510, a potem wyobraź sobie, jak okropnie będzie pracować przy tym stole.

Najpierw użyjmy schematu firmowego.

[Companies] ComnpanyId, COMPANY_NAME, CREATED_ON

Następnie użyjemy Twojego schematu Użytkownicy do uzyskania wymaganych atrybutów najwyższego poziomu, które będą używane / udostępniane przez wszystkie firmy.

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

Następnie tworzymy tabelę, w której zdefiniujemy nasze dynamiczne atrybuty, które są specyficzne dla niestandardowych atrybutów użytkownika każdej firmy. Więc tutaj przykładową wartością kolumny Attribute będzie „LikeMusic”:

[UserAttributeDefinition] UserAttributeDefinitionId, CompanyId, Attribute

Następnie definiujemy tabelę UserAttributes, która będzie przechowywać wartości atrybutów użytkownika

[UserAttributes] UserAttributeDefinitionId, UserId, Value

Można to zmienić na wiele sposobów, aby poprawić wydajność. Możesz użyć wielu tabel dla UserAttributes, dzięki czemu każdy z nich jest specyficzny dla typu danych przechowywanych w Value lub po prostu zostaw go jako VarChar i pracuj z nim jako magazynem wartości klucza.

Możesz także chcieć przenieść CompanyId ze tabeli UserAttributeDefiniton do tabeli porównawczej w celu późniejszego sprawdzenia.

Użyj bazy danych NoSQL. Będą dokumenty firmy i użytkownika. Użytkownicy mieliby część swojego schematu dynamicznie tworzoną na podstawie szablonu użytkownika (tekst wskazujący pola / typy dla tej firmy.

\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

Tak może wyglądać coś takiego jak Firebase.com. Musisz nauczyć się, jak to zrobić w dowolnym wybranym przez siebie.

Jeśli często masz zamiar spotykać się z niestandardowymi żądaniami pola, właściwie modelowałbym to bardzo podobnie do bazy danych. Utwórz tabelę zawierającą metadane dotyczące każdego pola niestandardowego, CompanyCustomField (do kogo należy, typu danych itp.) Oraz inną tabelę CompanyCustomFieldValues, która zawiera CustomerId, FieldId i wartość. Jeśli używasz czegoś takiego jak Microsoft Sql Server, kolumna wartości miałaby typ danych sql_variant.

Oczywiście nie jest to łatwe, ponieważ potrzebny jest interfejs, który pozwala administratorom definiować pola niestandardowe dla każdego klienta, a także inny interfejs, który faktycznie używa tych metadanych do tworzenia interfejsu użytkownika w celu gromadzenia wartości pól. A jeśli masz inne wymagania, takie jak grupowanie pól razem lub konieczność utworzenia pola z listą wyboru, musisz dostosować go do większej liczby metadanych / innych tabel (np. CompanyCustomFieldPickListOptions).

Nie jest to trywialne, ale ma tę zaletę, że nie wymaga zmian w bazie danych / zmian kodu dla każdego nowego niestandardowego pola. Wszelkie inne funkcje pól niestandardowych również będą musiały zostać zakodowane (na przykład jeśli chcesz ponownie sprawdzić poprawność wartości ciągu lub zezwolić tylko na daty między określonymi zakresami lub jeśli chcesz włączyć jedno pole niestandardowe na podstawie innej wartości pola niestandardowego ).

Alternatywą dla innych odpowiedzi jest tabela o nazwie profil_atrybut lub podobny, że schemat jest całkowicie zarządzany przez aplikację.

W miarę dodawania niestandardowych atrybutów ALTER TABLE profile_attrib ADD COLUMN like_movie TINYINT(1)możesz zabronić ich usuwania. Pozwoli to zminimalizować łączenie, jednocześnie zapewniając elastyczność.

Wydaje mi się, że nieco kompromis polega na tym, że aplikacja potrzebuje teraz uprawnień do zmiany tabeli w stosunku do bazy danych i musisz być sprytny w kwestii dezynfekcji nazw kolumn.

Twoje pytanie ma wiele potencjalnych rozwiązań. Jednym z rozwiązań jest przechowywanie dodatkowych atrybutów jako XML. XML może być przechowywany jako tekst lub jeśli korzystasz z bazy danych, która obsługuje typy XML jako XML (SQL Server). Przechowywanie jako tekst ogranicza twoją zdolność zapytań (jak wyszukiwanie niestandardowego atrybutu), ale jeśli przechowywanie i wyszukiwanie jest wszystkim, czego potrzebujesz, to jest to dobre rozwiązanie. Jeśli trzeba zapytać, lepszym rozwiązaniem byłoby przechowywanie XML jako typu XML (chociaż jest to bardziej specyficzne dla dostawcy).

Umożliwi to przechowywanie dowolnej liczby atrybutów dla klienta, po prostu dodając kolumnę dodawania do tabeli klientów. Można przechowywać atrybuty jako hashset lub słownik, stracisz bezpieczeństwo typu, ponieważ wszystko będzie ciągiem od samego początku, ale jeśli wymusisz standardowy format formatu dla dat, liczb i wartości logicznych, to zadziała OK.

Po więcej informacji:

https://msdn.microsoft.com/en-us/library/hh403385.aspx

Odpowiedź @ WalterMitty jest również ważna, chociaż jeśli ktoś ma wielu klientów o różnych atrybutach, może dojść do wielu tabel, jeśli zastosuje się model dziedziczenia. To zależy od liczby niestandardowych atrybutów udostępnianych klientom.

Powinieneś znormalizować bazę danych, tak abyś miał 3 różne tabele dla każdego rodzaju profilu firmy. Korzystając z Twojego przykładu, masz tabele z kolumnami:

USER_ID, LIKE_MOVIE, LIKE_MUSIC

USER_ID, FAVORITE_CUISINE

USER_ID, OWN_DOG, DOG_COUNT

Takie podejście zakłada, że ​​będziesz znać kształt informacji, które firma chce przechowywać przed ręką i że nie będzie się często zmieniać. Jeśli kształt danych jest nieznany w czasie projektowania, prawdopodobnie lepiej byłoby użyć tego pola JSON lub bazy danych nosql.

Z tego czy innego powodu bazy danych to jedyne pole, w którym najczęściej pojawia się efekt wewnętrznej platformy. To tylko kolejny przypadek pojawienia się anty-wzoru.

W tym przypadku próbujesz walczyć z naturalnym i poprawnym rozwiązaniem. Użytkownicy firmy A nie są użytkownikami firmy B i powinni mieć własne tabele dla własnych pól.

Twój dostawca bazy danych nie pobiera opłat za tabelę i nie potrzebujesz dwa razy więcej miejsca na dysku dla dwóch tabel (w rzeczywistości posiadanie dwóch tabel jest bardziej wydajne, ponieważ nie przechowujesz atrybutów A dla użytkowników B. Nawet przechowywanie tylko wartości NULL zajmuje miejsce).

Oczywiście, jeśli istnieje wystarczająca liczba wspólnych pól, można je rozdzielić na współdzieloną tabelę Użytkownicy i mieć klucz obcy w każdej tabeli użytkownika specyficznej dla firmy. Jest to tak prosta struktura, że ​​żaden optymalizator zapytań do bazy danych nie ma z nią problemów. Wszelkie niezbędne DOŁĄCZY są trywialne.

Moje rozwiązanie zakłada, że ​​wywołujesz to zapytanie z programu i powinieneś być w stanie wykonać przetwarzanie końcowe. Możesz mieć następujące kolumny:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CUSTOM_VALUES

CUSTOM_VALUES będzie typu ciąg przechowujący parę kluczy i wartości. klucz będzie nazwą kolumny, a wartością będzie wartość kolumny np

LIKE_MOVIE;yes;LIKE_MUSIC;no;FAV_CUISINE;rice

w tym CUSTOM_VALUES zapiszesz tylko te informacje, które istnieją. Podczas zapytania z programu możesz podzielić ten ciąg i użyć go.

Używam tej logiki i działa dobrze, wystarczy, że będziesz musiał zastosować logikę filtrowania w kodzie, a nie w zapytaniu.