Re: SQL, related records (quotes)

Stefan Rybacki wrote:
> 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.

A good constraint would be a continious key (like an auto increment) and a parentID that has always to be smaller than the current nodeID. All those constraints leak on the update thing. The FK constraint gives to much room for inconsistency and the greater FK than the PK gives not enough freedom (e.g. if you want to move a node x that hasn't node y as child to y->x which is ok for the tree but not for the constraint)

Regards
Stefan Received on Sat Jun 25 2005 - 00:38:44 CEST