Results 1 to 6 of 6

Thread: Table Design

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Which would be more efficient?

    A table with 2 millon rows and 10 columns, or a table with 36 thousand rows and 61 coulumns?
    Richard

  2. #2
    New Lounger
    Join Date
    Jun 2010
    Location
    UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Pros and cons of both I think. I personally prefer normalised union queries ie the 10 column approach.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.

    That is where I was headed, but with that many rows I wanted to get some other views.
    Richard

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Aheron View Post
    Which would be more efficient?

    A table with 2 millon rows and 10 columns, or a table with 36 thousand rows and 61 coulumns?
    I'm trying to figure out the situation in which this could be a choice. I suppose the 2 million record table is normalized and contains multiple records for each something (person, thing, or whatever), and the 36,000 record table would have only 1 record for each something. Unless you can prove some overwhelming reason to decide otherwise, I'm going to select the normalized version every time.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    I'm trying to figure out the situation in which this could be a choice. I suppose the 2 million record table is normalized and contains multiple records for each something (person, thing, or whatever), and the 36,000 record table would have only 1 record for each something. Unless you can prove some overwhelming reason to decide otherwise, I'm going to select the normalized version every time.
    I would echo Mark's statement. Always go with normalized and deviate only when forced to do so. You'll have fewer headaches and more flexibility with that approach.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Correct, the 36,000 row table had one row for a Store with 52 columns of weekly sales data.
    The 1millon plus row table had one row for each Store / Week combination with a column for the sales data.

    I typically go for the normalized option as well. Just 1 million rows seemed like a lot, so I wanted to double check with folks that have more experience than I.


    Thanks
    Richard

Posting Permissions

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