...

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



Grant Palin
07-19-2004, 11:17 PM
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:


id 1990 1991 1992 1993 1994
1 ... ... ... ... ...
2 ... ... ... ... ...
3 ... ... ... ... ...


Idea 2:


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.

Grant Palin
07-20-2004, 12:24 AM
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.

Bullschmidt
07-20-2004, 08:00 AM
<<
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.

shmoove
07-20-2004, 08:57 AM
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

raf
07-20-2004, 12:38 PM
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)

Grant Palin
07-20-2004, 04:42 PM
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...

Grant Palin
07-20-2004, 05:28 PM
...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...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum