I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into /date/guid.cvs. The sync part is done but now I need to take the synced files and dump them into a database for a reporting system. This all gets stored on a client's computer so I need a file database for ease of use. I've been using sqlite.
Now for my question. Each row in the csv file is an event with data defined:
Timestamp, vehicle length (single), controller state (int), turn type (int), queue length (int), other ints defining the event
I was also planning to include the guid from the csv filename. The problem I see is that this single table will have many, many rows. Just on a small amount of data (couple days) I already have over 100k rows. This will need to handle potentially years worth of events.
Is there a good way to reduce the row amount or is it not going to be an issue? Am I missing some way to design the table(s) so they don't grow so large? I saw something about partitioning but I'm not sure sqlite supports that. Is there another file database that does? I'm using c# 4 as the front end for the client.