[qwelukasz]

#bazydanych #programowanie #mysql #postgresql

Mam takie pytanie do bardziej doświadczonych dev'ów.

Sprawa jest następująca.
Jest strona, na której osoba zakłada konto (podaje nazwę firmy, swoj login i hasło) i automatycznie staje się administratorem w obrębie swojego konta w danej firmie.
W swoim koncie ma możliwość tworzenia zwykłych użytkowników jak i administratorów.

Każdy użytkownik w miesiącu będzie generować średnio 50 rekordów w bazie. Zakładając że osoba która założyła konto doda 50 użytkowników to daje 2500 rekordów na miesiąc. Nie jest to jakoś super dużo ale idzmy dalej.

Kolejna osoba zakłada konto i tworzy 100 użytkowników (100*50 = 5 000 ).
Czyli przy dwóch 'firmach' mamy już 7 500 rekordów. I tak dalej i tak dalej..

Teraz moje pytanie, jaką strukturę powinna mieć baza aby była wydajna? (Raczej byłbym chciałbym iść w stronę #postgresql niźli #mysql głównie ze względu na bardziej zaawansowane funkcje jakie posiada baza.)
Czy lepiej byłoby stworzyć jedną tabelę np.
wpisy a w niej kolumny id_firmy, id_uzytkownika, id_dodajacego_rekord, wartosc, i jeszcze inne kolumny
i tak przeszukiwać tabele dla każdego zalogowanego pracownika ( + ewentualnie trzymać dane za ostatnie 3 miesiące a resztę przenosić do tabeli wpisy_historia) ?

Czy może w momencie gdy administrator zakłada konto tworzyć nową tabelę z nazwą firmy?

jakisPrefix_nazwaFirmy

a w niej dopiero powyższe kolumny? To sprawi że przy 30 różnych firmach będzie około ( 7 * 30 ) 210 tabel.

W obu przypadkach użytkownik musiałby podawać nazwę 'firmy' podczas logowania - inaczej nie będzie możliwości posiadania 2 użytkowników o takim samym loginie a zależy mi aby to zachować.

[legolass]

@qwelukasz: PostgreSQL i MySQL osiągają podobną wydajność, więc tu musisz wybrać tą bazę, którą wolisz.

7500 wpisów to dla bazy danych bardzo mało danych. Gdyby tych danych było miliardy to wtedy mógłbyś zaczynać zastanawiać się nad wydajnością.

Musisz oczyswiście pamiętać o założeniu odpowiednich indeksów na pola. Przede wszystkim ważny będzie indeks na id_firmy, żeby szybko wybierać dane dla poszczególnej firmy.

[MacDada]

gdy administrator zakłada konto tworzyć nową tabelę z nazwą firmy?

@qwelukasz: Nie

jaką strukturę powinna mieć baza aby była

[qwelukasz]

@legolass @MacDada: nie mam jeszcze SQLi dopiero jestem na etapie projektowania bazy i tego jak to powinno działać.

Tabela wpisy przypuszczam że będzie wyglądać mniej więcej tak:

CREATE TABLE ratherNotToSay (

row_id INTEGER NOT NULL AUTOINCREMENT COMMENT 'defines rowid

[legolass]

@qwelukasz:
1. Bazy danych mają typ reprezentujący datę, więc chyba lepiej używać go niż stringów.
2. Dla wpisów historycznych stworzyłbym osobną tabelę. W tym momencie żeby wyświetlić aktualne wpisy musisz dla każdego wpisu wybrać wpis z maksymalnym historyid. To powoduje konieczność użycia podzapytań, co znacząco wpływa na wydajność (to można obchodzić dodając pole trzymające informację, czy dany wiersz jest aktualny, ale mimo wszystko lepiej przenieść te dane do osobnej

[MacDada]

@qwelukasz: Co do wersjonowania rekordów, zobacz to: https://github.com/simplethings/EntityAudit

Nawet jeśli nie używasz Doctrine i Symfony, to idea tam zastosowana jest IMHO słuszna (dodatkowa tabelka trzymająca historię danych). W ten sposób nie zaśmiecasz głównej tabelki starymi danymi (jest lżejsza) i takie rzeczy jak id mogą być normalnie unique i autoincrement.

Co do samych tabelek – use case! Jakie zapytania chcesz robić? Tzn najpierw musisz wiedzieć co chcesz robić, żeby móc to

[qwelukasz]

1. Bazy danych mają typ reprezentujący datę, więc chyba lepiej używać go niż stringów.

@legolass: Czy w takim razie lepiej byłoby trzymać rowdate jako date w formacie yyyy-mm-01 i wyszukiwać dane gdzie row_date = '2015-04-01' niż row_date = '2015-04'`?

[MacDada]

@qwelukasz: Dla przykładu u nas:

* dosyć często u nas dodaje się głosy (czyli jest ich dużo, kilkadziesiąt milionów)
* prawie nigdy się ich nie edytuje
* bardzo często chcę znać liczbę głosów dla głosowanej

[qwelukasz]

@MacDada: mhmm no to podobnie będzie tak:

* bardzo często dodaje się wpisy
* bardzo często się je edytuje
* muszę znać znać aktualne wartości dla danego miesiąca i firmy
* rzadko chciałbym wiedzieć kto naniósł zmiany

[MacDada]

* rzadko chciałbym wiedzieć kto naniósł zmiany

@qwelukasz: To już np podpowiada, że zdecydowanie warto wywalić archiwalne rekordy do oddzielnej tabeli – nie będą wtedy przeszkadzać w:

* bardzo często dodaje się wpisy

* bardzo często się je edytuje

* muszę znać znać aktualne wartości dla danego miesiąca i firmy

[qwelukasz]

@MacDada: długo programujesz z symfony? (ʘ‿ʘ)

[qwelukasz]

@MacDada: a jak z tą datą? jak to lepiej trzymać?

@legolass: 1. Bazy danych mają typ reprezentujący datę, więc chyba lepiej używać go niż stringów.

Bo szukać będę po pełnych miesiącach więc musiałbym dodatkowo sprawdzać ostatni dzień w miesiącu i jechać zakresami
rowDate

[MacDada]

długo w SF2 programujesz? (ʘ‿ʘ)

@qwelukasz: Ciężko powiedzieć, zaczynałem jeszcze przy Symfony 1, ale w sumie realnie zrobiłem jedną appkę na Symfony 2 i drugą od pół roku przenoszę pod S2 (portal postawiony na starożytnym Nuke'u :D).

Więc czy długo? Nie wiem, po prostu używam przelotnie – samego frameworka, nie aż tak długo, ale już np komponenty wsadzałem do innych frameworków i używam od kilku lat

[legolass]

@qwelukasz: Nie jestem ekspertem od baz danych, ale osobiście trzymałbym pełną datą i używał BETWEEN. Z tego co wiem, jeśli na polu będzie założony indeks to between z niego skorzysta, czyli będzie to równie szybkie co trzymanie wszystkie z pierwszym dniem miesiąca, a informacja o pełnej dacie może Ci się przydać w przyszłości

[MacDada]

@qwelukasz: Ogólnie zaprojektuj sobie tabele tak, jak byś chciał z nich korzystać: żeby miały sensownie ułożone znormalizowane dane.

I dopiero jak będziesz mieć taki „idealny zestaw tabel” – wtedy zastanów się, co będzie boleć i optymalizuj.

Np masz usera i

[qwelukasz]

@MacDada: @legolass:
ok, dziekuje panowie. Dużo mi pomogliście i naprowadziliście mnie na właściwe tory rozumowania.
Zaplusowałem wszystkie wasze komentarze.

[MacDada]

@legolass: Co do daty:

1. poprawnie jest trzymać datetime
2. ale jak dostaniesz po dupie z wydajnością, możesz np wyciągnąć drugą kolumnę trzymającą sam miesiąc

Ale póki nie masz realnego zastosowania – ciężko przewidzieć gdzie będziesz obrywać po dupie, czyli gdzie optymalizaować.