Re: SQL, related records (quotes)
From: Stefan Rybacki <stefan.rybacki_at_gmx.net>
Date: Sat, 25 Jun 2005 00:33:29 +0200
Message-ID: <3i3g1qFjcqlgU1_at_individual.net>
>> Stefan Rybacki wrote:
>>
>>> mAsterdam wrote:
>>>
>>>> Jan Hidders wrote:
>>>>
>>>>> Stefan Rybacki wrote:
>>>>>
>>>>>> timpea_at_gmail.com wrote:
>>>>>>
>>>>>>> I have a website that users can request quotes, and a user may also
>>>>>>> make a new quote that links to a previous quote.
>>>>>>>
>>>>>>> QuoteNo PreviousQuoteNo
>>>>>>> 1
>>>>>>> 2
>>>>>>> 3 1
>>>>>>> 4 3
>>>>>>> 5 4
>>>>>>>
>>>>>>> If i request quoteNo 3 i want to have a list of all the related
>>>>>>> quotes.
>>>>>>>
>>>>>>>
>>>>>>> RelQuote
>>>>>>> 1
>>>>>>> 3
>>>>>>> 4
>>>>>>> 5
>>>>>>>
>>>>>>> Is this possible with a SQL statement? or would i be best doing a
>>>>>>> loop
>>>>>>> in asp and many requests?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Try the nested sets model.
>>>>>
>>>>>
>>>>>
>>>>> Are you sure the relation describes always a tree?
>>>>
>>>>
>>>>
>>>> Values like
>>>> QuoteNo PreviousQuoteNo
>>>> 3 6
>>>> 6 3
>>>>
>>>> or just
>>>> QuoteNo PreviousQuoteNo
>>>> 3 3
>>>>
>>>> are possible, so no tree. The columnnames suggest the OP does not
>>>> want this to happen. They don't help to prevent these loops though.
>>>
>>>
>>>
>>>
>>>
>>> That is true but these are inconsitent states. Its clear that you can
>>> create those table values by hand but think about it in algorithmic way:
>>>
>>> you want to create a quote with QuoteNo=3 so how can you say its
>>> parent is QuoteNo=6 since there is no quote with this number. Ok
>>> somebody can say what if QuoteNo=6 is already existing. Ok we go from
>>> there so how could this quote (6) have known from QuoteNo=3? since we
>>> are currently creating it?
>>
>>
>>
>>
>> Consider adding these constraints: QuoteNo as key,
>> and PreviousQuoteNo as foreign key to QuoteNo.
>>
>> Only now we have the intended tree-consistency.
>> The circular states mentioned above are inconsistent with the
>> constraints, and I can't add these values by hand or by algorithm.
Date: Sat, 25 Jun 2005 00:33:29 +0200
Message-ID: <3i3g1qFjcqlgU1_at_individual.net>
mAsterdam wrote:
> mAsterdam wrote: >
>> Stefan Rybacki wrote:
>>
>>> mAsterdam wrote:
>>>
>>>> Jan Hidders wrote:
>>>>
>>>>> Stefan Rybacki wrote:
>>>>>
>>>>>> timpea_at_gmail.com wrote:
>>>>>>
>>>>>>> I have a website that users can request quotes, and a user may also
>>>>>>> make a new quote that links to a previous quote.
>>>>>>>
>>>>>>> QuoteNo PreviousQuoteNo
>>>>>>> 1
>>>>>>> 2
>>>>>>> 3 1
>>>>>>> 4 3
>>>>>>> 5 4
>>>>>>>
>>>>>>> If i request quoteNo 3 i want to have a list of all the related
>>>>>>> quotes.
>>>>>>>
>>>>>>>
>>>>>>> RelQuote
>>>>>>> 1
>>>>>>> 3
>>>>>>> 4
>>>>>>> 5
>>>>>>>
>>>>>>> Is this possible with a SQL statement? or would i be best doing a
>>>>>>> loop
>>>>>>> in asp and many requests?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Try the nested sets model.
>>>>>
>>>>>
>>>>>
>>>>> Are you sure the relation describes always a tree?
>>>>
>>>>
>>>>
>>>> Values like
>>>> QuoteNo PreviousQuoteNo
>>>> 3 6
>>>> 6 3
>>>>
>>>> or just
>>>> QuoteNo PreviousQuoteNo
>>>> 3 3
>>>>
>>>> are possible, so no tree. The columnnames suggest the OP does not
>>>> want this to happen. They don't help to prevent these loops though.
>>>
>>>
>>>
>>>
>>>
>>> That is true but these are inconsitent states. Its clear that you can
>>> create those table values by hand but think about it in algorithmic way:
>>>
>>> you want to create a quote with QuoteNo=3 so how can you say its
>>> parent is QuoteNo=6 since there is no quote with this number. Ok
>>> somebody can say what if QuoteNo=6 is already existing. Ok we go from
>>> there so how could this quote (6) have known from QuoteNo=3? since we
>>> are currently creating it?
>>
>>
>>
>>
>> Consider adding these constraints: QuoteNo as key,
>> and PreviousQuoteNo as foreign key to QuoteNo.
>>
>> Only now we have the intended tree-consistency.
>> The circular states mentioned above are inconsistent with the
>> constraints, and I can't add these values by hand or by algorithm.
> > > Oops - still not good enough.
It should be good enough for an insert but not for an update, or did I
forget something?
Stefan