[SQLite] Projektowanie bazy danych - seria w kolumnach czy w wierszach ?

Witam!

piszę moduł do aplikacji obliczeniowej która będzie wykorzystywać dużo danych pomiarowych. Będą do np. pomiary co godzinę lub częściej przez cały rok. Pomiary te mogą zaczynać się oraz kończyć w różnym czasie. Nie wiem jak zaprojektować poprawnie tabele w bazie danych (SQLite) aby wszystko chodziło jak najszybciej bo szybkość jest bardzo ważna. Moja koncepcja jest taka (dwie tabele):

TabelaSeriePomiarow(identyfikator serii, data rozpoczęcia pomiarów, ilość pomiarów)

TabelaPomiary(identyfikator serii - nazwa wiersza?, pomiar 1, pomiar 2, ... pomiar n)

tylko jak bym sobie tego nie wymyślił to i tak wiem że coś źle robię, coś mi ciągle nie pasuje. Dlatego pytam Was: jak to ogarnąć żeby było dobrze?

Co do tabeli TabelaSeriePomiarow to zastanawiam się jeszcze nad jedną rzeczą... otóż czy jest sens tworzyć w tej tabeli dodatkowe pola opisujące np. maksymalną wartość lub średnią z serii pomiarów? czy za każdym razem gdy taka wartość jest mi potrzebna to pobierać całą serię pomiarów i obliczać te wartości?

Jeśli serie umieścisz w kolumnach, to narobisz sobie problemów. Np. wyliczeniem średniej z serii będziesz musiał się zajmować sam, bo żaden mechanizm SQLowy nie da rady.
TabelaPomiary(
Id integer not null primary key,
idSerii integer not null,
numerPomiaru integer,
wartosc ...
)
tak to z grubsza powinno wygladać.

heh tydzień bym myślał i bym nie wymyślił...

Marcin.Miga jedno spojrzenie na to co napisałeś i od razu wiem że to jest to :) tylko zamiast numer pomiaru będzie data pomiaru... no chyba że ktoś podpowie coś jeszcze lepszego...

Kolejne pytanie: czy przy (mniej więcej) takiej organizacji tabeli Pomiary jak napisał Marcin.Miga jest sens robić tabele SeriePomiarów w której były by np. data rozpoczęcia pomiarów, średnia wartość pomiarów, maksymalna wartość pomiarów, ilość pomiarów? czy za każdym razem lepiej "wyłuskiwać" sobie te dane z tabeli pomiary?

Jest sens. Przy dużej ilości pomiarów i/albo dużej ilości w jednej serii odczytanie pojedynczego rekordu jest dużo szybsze niż wyliczenie tego z poszczególnych pomiarów.

Jak najbardziej jest sens. Dzięki temu czas potrzebny na zagregowanie wszystkich pomiarów będzie "zużywany" po kawałku w odniesieniu do danej serii pomiarowej. Mając zagregowane dane o całych seriach, dalsze raportowanie, agregacja będą już o wiele szybsze. Oczywiście wszystko zależy od skali i ilości danych.

tom_85 napisał(a):

heh tydzień bym myślał i bym nie wymyślił...

Marcin.Miga jedno spojrzenie na to co napisałeś i od razu wiem że to jest to :) tylko zamiast numer pomiaru będzie data pomiaru... no chyba że ktoś podpowie coś jeszcze lepszego...

Kolejne pytanie: czy przy (mniej więcej) takiej organizacji tabeli Pomiary jak napisał Marcin.Miga jest sens robić tabele SeriePomiarów w której były by np. data rozpoczęcia pomiarów, średnia wartość pomiarów, maksymalna wartość pomiarów, ilość pomiarów? czy za każdym razem lepiej "wyłuskiwać" sobie te dane z tabeli pomiary?

Oczywiscie ze jest sens aby to wszystko rozbic na tabele. Do tabeli gdzie bedziesz wpisywac transakcje po prostu dodasz kolumne do ktorej bedziesz wpisywac date. Mozesz rowniesz uzyc trigera do automatyczego zapisu czasu wpisania rekordu (pomiaru) np: DATETIME('NOW')

aby zakończyć wątek napiszę co postanowiono :P

w bazie znajdą się dwie tabele odpowiedzialne za pomiary:
1 - tabela zawierająca zbiorcze informację o każdej z serii pomiarów o budowie mniej więcej takiej:
TabelaSeriePomiarów(id serii, maksimum serii, średnia serii, data rozpoczęcia pomiarów, ilość pomiarów w serii)
2 - tabela zawierająca pojedyncze pomiary każdej z serii o budowie mniej więcej takiej:
TabelaPomiary(id pomiaru, id serii, data pomiaru, wartość pomiaru)

Wydaje mi się że na chwilę obecną jest to najlepsze rozwiązanie (nie mam zbyt dużo czasu na napisanie tego modułu) jednak w przyszłości gdy tylko znajdę "wolną chwilkę" to przyjrzę się nierelacyjnym bazom danych bo bardzo możliwe że do tego celu było by to lepsze rozwiązanie niż SQLite :-) Tak że na chwilę obecną problem uważam za rozwiązany!

Dziękuję za pomoc - bez Was nie dałbym rady!

Jeśli system obliczeń ma działać bez przerwy, może warto trzymać dane w pamięci RAM i co jakiś czas zapisywać dane do bazy, wtedy podejmujesz ryzyko, że w czasie awarii (brak prądu) tracisz część danych, które nie były zapisane. Popatrz też, że SQLite możesz kompilować z wieloma flagami, które przyspieszają bazę danych, w operowaniu masz polecenia PRAGMA, w których możesz ustawiać wielkość strony, ilość trzymanego cache itp. Wtedy musisz sobie przeliczyć ile RAM możesz używać, ile danych będziesz trzymał. Kilka takich "trickow" i możesz kilkuset-krotnie przyspieszyć operacje na SQLite3.

agilob napisał(a):

(...) Popatrz też, że SQLite możesz kompilować z wieloma flagami, które przyspieszają bazę danych (...) Kilka takich "trickow" i możesz kilkuset-krotnie przyspieszyć operacje na SQLite3.

poszukam, poczytam jak masz jakieś ciekawe linki na ten temat pod ręką to ślicznie poproszę :-)

obliczenia będą uruchamiane bardzo, bardzo, bardzo rzadko ale podczas obliczeń będzie (może być) potrzebnych tyle danych że właśnie dlatego postanowiłem użyć bazy danych zamiast trzymać dane w pamięci.

Nie obawiaj się wielkości bazy. Zapisywania do pamięci ma tylko ta wadę ze można bardzo łatwo stracić dane. Jest to na pewno szybsze ale przy dobrym rozpisaniu bazy nie będziesz miał z tym problemu.
Używając wrappera na pewno będzie to szybkie. ( robię zapis 15.000 rekordów danych klientów z pliku ASCII do bazy SQLite w mniej niż 2 sekundy )
Podczas pierwszej próby miałem czas 28 sekund. Po prostu trzeba się troszeczkę z tym pomeczyc a wyniki mogą być imponujące.

a co mi tam... pociągnę temat dalej bo dobrze mi się z Wami pisze :P

piszę w Delphi .Net 1.1 i do komunikacji z SQLite chciałem użyć ODBC (System.Data.Odbc) co o tym myślicie? dobre rozwiązanie? czy istnieje lepsze, szybsze, bardziej uniwersalne dla Delphi .net 1.1?

lechk napisał(a):

Nie obawiaj się wielkości bazy.
W aplikacji którą rozwijam mogą pojawić się schematy np. z tysiącem elementów z których każdy będzie miał pomiary przez rok co 15 minut co daje dość spory rozmiar. W pamięci tego raczej nie ulokuje...

Obecnie męczę się z napisaniem jakiegoś fajnego interfejsu do baz danych aby w przyszłości rozszerzyć jeszcze funkcjonalność aplikacji o korzystanie z innych baz danych.

Ja pracuje na wxWidgets c++ i SQLite ( przez wrapper ). A co do rekordów to gdy bedżie tego aż tak dużo , może lepiej by podzielić te table np na rok?
Myśle ze na pewno znajdziesz na to sposób.

A ODBC jest najdłuższą z możliwych dróg.

To co zamiast ODBC tak żeby działało z SQLite pod Delphi .Net 1.1 i było darmowe?

i dlaczego ODBC jest najdłuższą z możliwych dróg ?

Do .NET użyj tego:
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

http://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q3 :

(3) What versions of .NET Framework are supported?

The .NET Framework 2.0 SP2 (or higher) for the System.Data.SQLite assembly.

The .NET Framework 3.5 SP1 (or higher) for the System.Data.SQLite.Linq assembly.

All sub-projects are fully supported with the .NET Framework 4.0.

z tego co widzę to .net 1.1 niestety nie jest wspierany :/

jakieś inne propozycje?