Re: SQL, related records (quotes)

<timpea_at_gmail.com> wrote in message
news:1119628561.458253.110450_at_g44g2000cwa.googlegroups.com...
> Hi,>> 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?

You could model this as a binary equivalence relation and merge it with your
original "table". Since you are interested in relationships that are
reflexive, symmetric, and transitive in nature (in contrast to directed
one-way relationships), your biggest design decisions would be:
1. Deciding what symbolic notation or surrogate value to use to identify
the distinct equivalence classes.
2. Providing under the covers or explicit application logic to automate
assignment of equivalence identity correctly for inserts, deletes, and
updates.

There is more complexity for updates with this solution, but far less for
queries, which would scale much better than most other ways of calculating
closure "on the fly" with large chains or families.

As you can see, the relation accounts for groupings of related quotes as a
quote chain or quote family. In this case, I decided to use the arbitrary
rule of taking the identifier with the least numeric value within a given
chain as the identifer for the grouping. The trade-off is the complexity in
recalculating closure when updates such as value changes, reassignment, or
deletions occur. If these occurances will generally be rare, this is a
pretty fast way to query closure of equivalence.