From: Peter Brawley
Date: June 19 2007 2:55pm
Subject: Re: Storing a linked list
List-Archive: http://lists.mysql.com/mysql/207573
Message-Id: <4677EE64.4050707@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matt

 >I'd like to store paths to specific destinations...

See
-- Tropashko's 'materialized modell' eg 
http://www.dbazine.com/oracle/or-articles/tropashko4
-- the airports example at 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

PB

-----

Matt Juszczak wrote:
> Hi all,
>
> I've got a table such as the following:
>
> id1 char
> id2 char
>
> sample data looks like this:
>
> id1    id2
> 1    3
> 2    4
> 3    5
> 5    6
> 6    8
>
> And of course another table has something like:
>
> id    info1    info2    info3
> 1    blah    blah    blah
> 2    blah    blah    blah
>
> I'd like to store paths to specific destinations...
>
> In other words, the path from 1 to 8 is:
>
> 1,3,5,6,8
>
> I was thinking of creating a table called relationships
>
> start    end    path
> 1    8    {3,5,6}
>
> This would allow me to easily display the path if I know the start and 
> end, but what it doesn't allow me to do is reuse the data.
>
> IE: say that I calculate the path from 1 to 8 as 1,3,5,6,8, and then I 
> want to know the path from 3 to 6.  even though this is already 
> calculated, I have to recalculate it as another row... hence
>
> start    end    path
> 1    8    {3,5,6}
> 3    6    {5}
>
> I considered making another table, called hops, such as:
>
> start    end    relationshipID
> 1    8    1
>
>
> table hops:
> relationshipID    start    end
> 1        1    3
> 1        3    5
> 1        5    6
> 1        6    8
>
> Then I could almost "reuse" those hops somehow.... but not sure.
>
> Can anyone recommend a good way to store this data?
>
> Thanks!
>
> -Matt
>