Projekt tabeli użytkownicy

Witajcie.
Mam tabele USERS składającą się z:

• id
• login
• password

Potrzebuję dodać teraz pola: wiek, miasto, ulica, kod pocztowy etc. W sumie ok 30 kolumn.

W jaki sposób najlepiej to zrobić? Jako dodatkowe kolumny w tabeli USERS?

To zależy. Pokaż wszystkie kolumny razem z typami.

Wiek? Co roku będziesz robił UPDATE tej wartości? Może lepiej data urodzenia? Ostatecznie rok urodzenia jak nie masz pełnej daty.

Używanie takiej kolumny jak wiek to błąd projektowy. Możesz mieć osobne tabelki na telefony, adresy itp bo zazwyczaj jest to relacja jeden do wielu.

Tzn wiek faktycznie jest błędem. Powinno być raczej rok urodzenia.
Zastanawiam się który wariant jest lepszy:

1. Zrobienie jednej tabelki z 30-40 kolumnami zawierającymi poszczególne dane,
2. Zrobienie minimalistycznej tabeli USERS + tabeli zawierajace parametry użytkownika (nazwa parametru + wartość).

1. Zrobienie jednej tabelki z 30-40 kolumnami zawierającymi poszczególne dane,

Jeżeli wszystkie spośród tych 30 kolumn mają sens i są w relacji 1:1 do użytkownika, to czemu nie?

2. Zrobienie minimalistycznej tabeli USERS + tabeli zawierajace parametry użytkownika (nazwa parametru + wartość).

Taka tabelka ma sens dla czegoś w stylu key-value store, albo innego tworu, gdzie praktycznie wszystkie dane są opcjonalne.

Ogólnie rzekłbym, zależy, co chcesz tam trzymać. Pamiętaj, że możesz też rozbić dane na kilka tabel. Jeżeli chcesz trzymać np. historię adresów, to raczej więcej sensu będzie miało, zamiast w tabeli użytkowników trzymać adres1, adres2, adres3, et cetera, mieć w tabeli użytkowników obecnyAdres, a dane historyczne trzymać w osobnej tablicy, Tak samo np. numery telefonów czy adresy e-mail, jak @ralf napisał - albo wszystko w tabeli pomocniczej, albo ew. jedna kolumna w tabeli użytkownicy (wyznaczająca główny numer/adres) i reszta do tabeli pomocniczej.

Dodaj drugą tabele na dane które mogą się zmienić. Podziękujesz później.

kate87 napisał(a):

Dodaj drugą tabele na dane które mogą się zmienić. Podziękujesz później.

Dlaczego Twoim zdaniem lepiej tabela pomocnicza, niż wszystko w jednej tabeli? :)

Jak najlepiej zrobić insert/update w takim przypadku? Robiąc insert do tabeli USERS robi się 30 insertów do tabeli pomocniczej? Analogicznie update?

Takie rozwiązanie nie skomplikuje wyszukiwania (np pod względem wydajności)?

Chodzi o to, że dzielisz dane na nagłówek: id, login i hasło a resztę ładujesz w inną tabelkę połączoną kluczem do nagłówka. Ma to sens, jak np będziesz potrzebował wersjonować te dodatkowe dane, czyli zmiana adresu spowoduje dodanie nowych danych, a nie nadpisanie poprzednich, jeśli nie, to nie ma to znaczenia czy zrobisz jedna, czy dwie tabelki. 30 kolumn to nie powinno być wyzwaniem nawet dla MySQLa.

Poczytaj o postaci normalnej. Postać normalna obejmuje jedna tabelkę usera i druga z danymi na temat usera. W momencie kiedy będziesz chciał wyciągnąć dane na temat kilkunastu rekordów tego usera np ile razy zmieniał miejsce zamieszkania to robisz zwykły left join zamiast kilku crossów.

Poczytaj o postaci normalnej. Postać normalna obejmuje jedna tabelkę usera i druga z danymi na temat usera.

Pierwsze słyszę, żeby postać relacji normalnej definiowała jak ma wyglądać tabela usera i i jego dane. Zawsze mślałem, że chodzi o niepowtarzanie (redundancje) danych...

W momencie kiedy będziesz chciał wyciągnąć dane na temat kilkunastu rekordów tego usera np ile razy zmieniał miejsce zamieszkania to robisz zwykły left join zamiast kilku crossów.

Albo chwalisz się znajomością zlączeń w SQL, albo nie rozumiesz co piszesz... Może mam małą wyobrażnie, ale po co mi CROSS JOIN w tym hipotetycznym zadaniu?

Ja bym starał się rozważyć co i jak będzie się zmieniać i jak mocno będziesz chciał walidować. Przykład: chcesz mieć pewność, że ulica i miasto istnieją, to może lepiej mieć oddzielne tabele na nie, a w Users dawać tylko id miasta. Jeśli ulica, miasto nie są wartościami w odpowiednich tabelach to nie zapisujesz rekordu.
To powinno też zapobiec rozrostowi bazy.
Inna sprawa to kwestie różnych form adresu jeśli np. chcesz obsłużyć wiele krajów. Są kraje gdzie do adresu powinno stosować się formę: ulica, miasto, prowincja/województwo. Są takie gdzie nie ma kodu pocztowego. I teraz zrobisz sobie kolumnę "zip" jako not null, to nie obsłuży przypadku takiego kraju.
Podsumowując, chyba najlepiej sam musIsz przemyśleć jakie dane chcesz dopuszczać.

To co dla innych jest "najlepiej", dla Ciebie może być "najgorzej".

Wszystko zależy jakie masz potrzeby i co będziesz robił na tym modelu. Jaki stosunek ilości odczytów do zapisów przewidujesz? Co będzie tylko do odczytu, co się będzie często zmieniać, ile transakcji równoległych, jaki przyrost danych itp.

Ile tych atrybutów Usera będziesz potrzebował w aplikacji, bo jakoś trudno mi uwierzyć, że w 90% przypadków będzie chodziło o wszystkie atrybuty. Jak masz "workload", w którym 50% przypadków użycia korzysta z 3 tych samych atrybutów, 30% z 5 tych samych atrybutów, a 15% z 7 tych samych atrybutów, a później dłuuuugi ogon przypadków użycia, które korzystają z jakichś dziwnych kombinacji atrybutów, to może warto zrobić model fizyczny, które będzie zoptymalizowany pod te 95% przypadków użycia?

Odnośnie pomysłu ładowania wielu kolumn do tabelki, to niektóre silniki bazodanowe mają coś takiego jak "row chaining". Jak wrąbiesz za dużo kolumn, to dane mogą "wylać się" się poza rozmiar bloku I/O, wówczas jeden wiersz trzeba zapisać na >1 bloku danych. W efekcie do odczytu wszystkich kolumn potrzeba >1 operacji I/O. Słabo jeśli chodzi o wydajność. 50% spadek wydajności na IO już na starcie ;-)

W MySQLu "row size limit" = 64kb, więc sporo, ale niektórzy mają mogą mieć fantastyczne wyobrażenia o możliwościach bazach danych i ładować tam więcej.

Osobiście robiąc jakąś strukturę na bazie, zawsze zastanawiam się jaki będzie narzut na I/O i czy buforowanie bloków takiego obiektu w RAM, nie będzie marnotrawstwem.

@kate87: nie wiem czemu odpowiadasz w komentarzach, skoro post dotyczył wątku. Po kolei:

Postać normalna daje nam Zachowanie atomowość danych zamiast klonowania userów w zależności od tego, że na przyklad zmieniło im się nazwisko, a ja potrzebuję historii.

Klonowanie rekordu to redundancja, mogę mieć skolonowany rekord i oba będą przechowywały dane atomowe. jeżeli zmienia sie nazwisko, a nie potrzebuje jego historii to po prostu zmieniam ten atrybut i to będzie jak najbardziej zgodne z postacią normalną

W takim przypadku masz crossa bo masz dwóch userów,.a chodzi o jedną osobę. A tak ładnie wypychacz sobie dane do drugiej tabelki i tam może im się zmieniać nazwisko, dane adresowe, miejsca pracy, itd.

Czyli nie rozumiesz tego co piszesz... Pytałem po co CROSS nawet jeżeli założę, że w twoim hipotetycznym zadaniu mam sklonowane rekordy w users:

I zrobię cross join to otrzymam taki zbiór:

I nie bardzo mogę pojąć PO CO mi ten cross?