Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Newbie Design Help for large amounts of Data

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anoncat View Post
    I just thought I would seek some advise on if there is a more optimal approach to handling the data given my needs.
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    4
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please tell me all the columns in your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Posts
    4
    Of course, it is simply 1 column for the time of submission and then another column for each day of the year.

  6. #6
    Join Date
    Jan 2012
    Posts
    4
    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?
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anoncat View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •