Re: Normalized Tables & SELECT [was: Find "smallest common year"]

Stefan Schwarzer wrote:
>
>> An entirely different question is whether it is a good idea to write a
>> range as a value that the database cannot interpret correctly (referring
>> to the '1970-75' notation). You cannot group records by value this way
>> if you need to (for example) combine data from '1970' with data from
>> '1970-75'.
>>
>> But you seem to use these values just for labels, which I assume are
>> unique across years (eg. if you have a value '1970-75' you don't have
>> values '1970', 1971'..'1974'), in which case this is safe to use. As
>> pointed out by several people earlier, they make an excellent foreign
>> key too (provided they're unique).
>
> Yep, this is question I posed myself too. In the moment, when doing for
> example "per Capita" calculations on the fly of a variable which has
> something like 1970-75, I would then sum up the Total Population over
> the given period, divide it through the number of years and then use it
> with the selected variable to get the "per Capita" data.
>
> But if I would instead insert yearly data, it would mean that it had
> five lines with the same values. No problem with that?

Not entirely what I suggested, but also a viable solution, sure.

I was suggesting to add a column to your yearly data marking the end of
the range. Given your above examples, you could then do queries like:

SELECT population / num_years FROM my_data;

(Assuming you add the length of the interval as a number of years, which
seems plausible because you don't seem to calculate with any intervals
not dividable by a year).

Adding this additional column may justify putting the years (and their
durations) into their own table.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //