5

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.

Improve this question
4
  • 2
    What data do you need to get out of the database - perhaps you can store summaries instead of every row, perhaps you can archive off old data but it all depends on what you are using the data for
    – Jack Douglas
    Sep 28, 2011 at 6:16
  • That's an idea but not sure it'll work. So far we generate 3 report types. Each type has 2 groupings and all done by timestamp interval (every 15,30,60 mins). That should be easy to store in summary format but I'm not sure what new report types might be requested based on data in the event that we don't currently use. Because of that I'm hesitant to just store the summaries. One idea I was thinking about is having a table that stores date and a table name. Then create a table for each day. Then I can join the tables. But I'm not sure how well that will work with the reporting library.
    – knoxcoder
    Sep 28, 2011 at 10:30
  • 1
    What you are suggesting is a kind of manual date-based partitioning - the implication is that your queries usually look at data from a limited number of dates, is that true? To suggest how to store the data, we really need to know more about how the data is likely to be queried, but there is nothing inherently wrong with a table containing billions of more rows in principle. Why not load a couple years of sample data and see how sqlite copes with some sample queries?
    – Jack Douglas
    Sep 28, 2011 at 11:26
  • So far the only queries are time-sliced events grouping them by the various ints in the event row. Our system is a camera(s) that control red lights at intersections. Our clients like to see how many cars have gone through each lane based on the day and time. Beyond that they also like to see what the light state was, ie did they run a red or yellow. What type of car based on length. Did they turn illegally, ie a u-turn. How many cars are waiting at a red light. How long since the last green. They can also group based on approach, north/east/south/westbound or by all lanes.
    – knoxcoder
    Sep 28, 2011 at 12:20

1 Answer 1

Reset to default
1

Looking at your table structure means that while the actual row is small (many int values) there will be millions of rows.

Yes you can use a database of your choice, MySQL, Oracle, SQL Server, PostGres for the raw data from the cameras.

You can add partitioning by day which will create a partition for each day, and you may want to consider archiving data on an annual basis into a separate table for each year to make the table sizes manageable.

I would recommend that you setup another set of summary tables for reporting which can be based on the partitions, and this can be done on a daily basis (per partition) and automated.

The actual design of the summary tables depends on the reports you need to run, but it will be necessary so that your queries do not run off the base data table which will be huger.

Improve this answer
2
  • Do not use SQLite or MS Access as these will not be able to handle the data numbers
    – Stephen Senkomago Musoke
    Feb 27, 2012 at 12:16
  • We ended up just parsing the csv files in their folder structure which worked out well. It was a quick fix since I didn't have lots of time to work on it but vNext is coming so this will be revisited. I was actually looking at a no-sql db like ravendb (because of c#) since I could use it to handle more than just the counting data that I would like to store.
    – knoxcoder
    Feb 28, 2012 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.