[TriLUG] Any SQL gurus willing to help?

[Andrew Perrin]

On Tue, 18 Dec 2007, Jeremy Portzer wrote:

> Kevin Kreamer wrote:
>> I haven't tried it, but something like below should work:
>>
>> UPDATE attempts
>> SET final = true
>> WHERE NOT EXISTS
>>   (SELECT 1
>>   FROM attempts a2
>>   WHERE attempts.phone_num = a2.phone_num
>>   AND a2.attempt_when > attempts.attempt_when);
>>
>> I do need to point out that I'd be concerned about database normalization
>> with respect to a final column, given how you've laid out the tables here.
>> Just FYI.
>
> I agree that adding a column like this isn't the "best" way to do things
> based on the normalization rules.  It seems that just using the latest
> attempt_when should be sufficiient, and this column isn't needed at all.
> However as long as the final column is being created, it might be a good
> idea to set up a trigger that resets these columns when a new survey
> attempt is added.   Otherwise it could be easy for this to get "out of
> date" making other queries that depend on it return wrong results.
>
> --Jeremy
> -- 
> TriLUG mailing list        : http://www.trilug.org/mailman/listinfo/trilug
> TriLUG Organizational FAQ  : http://trilug.org/faq/
> TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
>

In theory I agree entirely. In practice, this is an entirely static 
database -- the data are dumped from an outside application and only read, 
not written to, here. So that's why I'm less worried than usual about 
normalization.

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin (at) unc.edu - http://perrin.socsci.unc.edu
Associate Professor of Sociology; Book Review Editor, _Social Forces_
University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA