Hello,

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.

one column per day of the year is definitely poor design

i didn't understand what you meant by "each row representing unit sales for that day of the timestamp"

Sorry, english is not my best language. I think I mean to say that each row in the database table represents a single day of unit sales for the timestamp in that row..

I know that is bad design but do you perhaps have the ability to enlighten me for a better solution?

could you please tell me all the columns in your table

Of course, it is simply 1 column for the time of submission and then another column for each day of the year.

Hi,

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?

no!

not if you have separate tables for each month, with columns for all the days in the month

go back to the design that had only two columns