Re: Nearest Common Ancestor Report (XDb1's $1000 Challenge)

On 21 May 2004 13:21:24 -0700, Neo wrote:

Hi Neo,

>> > Among other things, the difference in normalization between the
>> > implementations is quite different.
>>
>> It should not matter HOW my implementation generates the report.
>
>It does matter because a less generic and unnormalized implementation
>will cause more problems or even fail under a broader scope.
>
>Here is another example. Suppose the user enters 'john' in tbl_thing
>and then enters 'john' in other tables. Later he tries to change
>'john' to 'johnny' in tbl_thing. I tried, but I cant because of
>constraints to keep redundant data in multiple tables from becoming
>unsynchronized (UPDATE statement conflicted with COL REF constraint
>'FK_types_thing_0D'. This conflict occured in db 'Test', table,
>'types', col 'thing'. Statement terminated.)

You're right. I had forgotten to include DRI actions when I posted the script for my implementation. Everywhere I wrote "references things", I should have written "references things on update cascade on delete cascade". Due to a silly limitation of SQL Server, this can only be done for one of the foreign keys in the hierarchies table; I'd have to write a trigger to get the same result for the other one. (Both the DRI actions and the trigger won't affect the report writing performance in any way.)

I'm perfectly willing to supply the code for the trigger if necessary to convince you, but not now - it's already 2 AM over here, so I'm off to bed.

>In XDb1, user can change 'john' to 'johnny' and the report runs as
>before.
>
>Using data itself to link redundant data is a non-generic solution.

Huh? Please elaborate.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)