Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post

    Is it better to have one column for each year, or a column for all years?

    I have to create a database table for yearly data, for the last 20 years, for multiple regions.

    The first idea that came to mind was that I could create a column for each year, since there is a number for the yearly total for each region.

    Or would it be better to create a single column for all the years for each region, and a column for the data for each region and each year?

    Idea 1:
    Code:
    id     1990   1991   1992   1993   1994
    1       ...       ...      ...       ...      ...  
    2       ...       ...      ...       ...      ...  
    3       ...       ...      ...       ...      ...
    Idea 2:
    Code:
    id     year     total
    1     1990      ...
    1     1991      ...
    1     1992      ...
    1     1993      ...
    1     1994      ...
    2     1990      ...
    2     1991      ...
    2     1992      ...
    2     1993      ...
    2     1994      ...
    If it matters, a query run on the data needs to show the results with the years going across the top, and the regions down the side, so I'm leaning somewhat towards the first option.
    Last edited by Grant Palin; 07-19-2004 at 11:21 PM.

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Seems to me that it would be easier to run a query using the first structure, since you could just select each of the year columns. But if there is a single column for year, displaying each year for each region gets messier. As far as I can tell, it would probably involve looping through each region, then each year for that region, and then executing a query for the current region and current year. With the first method, you could just do a single select, without all the looping.

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    <<
    The first idea that came to mind was that I could create a column for each year...
    >>

    I'd vote against that option as it doesn't sound like good database normalization.

    It reminds me of people I've known who have created different tables for different years when they could just add a year field and store all the years' data in one table.
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  • #4
    Regular Coder
    Join Date
    Dec 2003
    Posts
    367
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Definitely option 2.

    With option 1, if you then want to add more years you will have to change the table's structure. It will also make it very difficult to run queries like "get the total between year x and year y" dynamically (where x and y are user inputs for instance).

    shmoove

  • #5
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by shmoove
    Definitely option 2.

    With option 1, if you then want to add more years you will have to change the table's structure. It will also make it very difficult to run queries like "get the total between year x and year y" dynamically (where x and y are user inputs for instance).

    shmoove
    i second that. Try to run a query wher you'd like to get the records returnsed so that they are sorted by region, and then sorted on the years (highest sailstotal first or so). Or to get the sum() for each region ...

    Option 1 is a typical outputformat/presentationformat, but it's not th best way to store the data in. (Unless you very frequently need to deliver exactly that output (maybe the case) and if it are static data (certainly the case, unless thevalues for pass years can still change) and if it takes to much processingtime to generate the crosstab at runtime (certainly not the case, unless you have a very high number of regions)
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Good arguments. But with method 2, how could I run a query so that I get years across the top of the table and regions down the side? With a single year column, selecting the year and total gives you those rows, but not as columns...

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by raf
    ...and if it takes to much processingtime to generate the crosstab at runtime (certainly not the case, unless you have a very high number of regions)
    What do you mean by "crosstab"?

    And there are about 185 regions involved...


  •  

    Posting Permissions

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