I'm developing an application in C# using sqlite that tracks unit sales daily, for the entirety of the current year. Currently I have one monolithic table with a column of each day of the year and one for the timestamp of when the sales were recorded, and each row representing unit sales for that day of the timestamp.
To complicate things further I need to be able to access the data to compare unit sales based on the sell date (so say compare the 1st of the month to the 2nd to see what has been sold for what dates in that time period) as well as the sell date (for example to look at the first Saturday of March and determine what dates sold the most for that day in the year).
I apologize if this is a bit much to ask but I've not really had to deal with such a large amount of data before and want to utilize the best design for the database to keep the queries as quick as possible. I just thought I would seek some advise on if there is a more optimal approach to handling the data given my needs. Thank you for your time and patience.
I attach some example in text to illustrate my data model. So i made some changes from original. I use 1 table to manage all times a user adds data in the index table and each months data for the year in a separated table. Now the tricky part I think I can use a database for each year this would keep the current working database smaller as data from older years is not necessary on a day to day basis but needs to be available if needed. Is this an efficient way to handle this data?