[Chicago-talk] creating relational keys

[Jay Strauss]

Thanks Imran.
I might play with Tree::Simple going forward

Jay

On Thu, Mar 1, 2012 at 2:04 PM, imran javaid <imranjj at gmail.com> wrote:

> Using a Tree is likely overkill, but you can build one like this:
>
> my $tree = Tree::Simple->new({id=>0,name=>'USA'}, TreeSimple->ROOT);
>
> # assume @data is your table sorted by ParentID
> foreach my $row (@data) {
>   ($id, $name, $pid) = @$row;
>   $tree->traverse( sub {
>     my ($_tree) = @_;
>     my $nodeData = $_tree->getNodeValue();
>     if ($nodeData->{id} == $pid) {
>       $_tree->addChild(Tree::Simple->new({id=>$id, name=>$name});
>     }
>   });
> }
>
> You could define a hash with level information:
> my %levels = (-1 => "Country", 0=> "State", 1=> "City", 2=> "Zip");
> so in a traverse routine you can tell which level you are on:
> print $level{$_tree->getDepth()};
>
> -imran
>
>
> On Thu, Mar 1, 2012 at 1:14 PM, Jay Strauss <me at heyjay.com> wrote:
>
>> Hi, yes (unfortunately) it all has to go in the same table.
>>
>> I'm using a vendor supplied data model, with a generic structure like;
>>
>> ID NAME ParentID
>>
>> Although, even if I could break it up into separate tables I'd still need
>> the keys
>>
>>
>> On Thu, Mar 1, 2012 at 1:11 PM, Chris Hamilton <cjhamil at gmail.com> wrote:
>>
>>> Just out of curiosity, is there a strong need to put all of this data
>>> into the same table?  At least with your example data it seems like
>>> the hierarchy is better managed through separate tables for city,
>>> state, and zip (with a 1:many relationship from state to city, and
>>> then from city to zip).  In that form it would be easy enough to
>>> create the ID's and tie them up as you iterate through your hash keys.
>>>  Just a thought.
>>>
>>> -Chris
>>>
>>> On Thu, Mar 1, 2012 at 12:58 PM, Jay Strauss <me at heyjay.com> wrote:
>>> > Imran,
>>> >
>>> > Thanks.  I'm ok with building a tree, currently I'm using a hash. I
>>> just
>>> > don't know how to unwind the tree and assign the proper ID and parent
>>> ID.
>>> >
>>> > Jay
>>> >
>>> >
>>> > On Thu, Mar 1, 2012 at 12:51 PM, imran javaid <imranjj at gmail.com>
>>> wrote:
>>> >>
>>> >> One option would be use a tree data structure. Take a look at
>>> >> Tree::Simple. You will have USA in the first level, states in the
>>> second,
>>> >> cities in the third, and zip codes in the 4th (and then whatever else
>>> below
>>> >> that).
>>> >>
>>> >> -imran
>>> >>
>>> >> On Thu, Mar 1, 2012 at 12:40 PM, Jay Strauss <me at heyjay.com> wrote:
>>> >>>
>>> >>> Hi all,
>>> >>>
>>> >>> I have some data like:
>>> >>> CITY|STATE|ZIP
>>> >>> SCHAUMBURG|IL|60194
>>> >>> MATTESON|IL|60443
>>> >>> WARRENTON|OR|97146
>>> >>> MOUNTAIN HOME|AR|72653
>>> >>> FORT WORTH|TX|76107
>>> >>> CLEVELAND|MS|38732
>>> >>> WATERTOWN|SD|57201
>>> >>> GRAND CHUTE|WI|54913
>>> >>>
>>> >>> I want to load it into a relational database in such a way that I
>>> have
>>> >>> the proper keys to build a hierarchy.
>>> >>>
>>> >>> so for example:
>>> >>> ID Name ParentID
>>> >>> 0 USA
>>> >>> 1 IL 0
>>> >>> 2 SCHAUMBURG 1
>>> >>> 3 60194 2
>>> >>> 4 MATTESON 1
>>> >>> 5 60443 4
>>> >>> 6 OR 0
>>> >>> 7 WARRENTON 6
>>> >>> 8 97146 7
>>> >>> ...
>>> >>>
>>> >>> I'm not sure of a good way to do this.
>>> >>>
>>> >>> I read the data an built a hash like:
>>> >>> USA => {IL => {SCHAUMBURG => {60194 => 0},
>>> >>>       MATTESON   => {60443 => 0}},
>>> >>> OR => {WARRENTON  => {97146 => 0}}
>>> >>> ...
>>> >>> };
>>> >>>
>>> >>> I can't think of a good way to look through it and assign the keys.
>>> >>>
>>> >>> Maybe someone has done this in the past and has an elegant solution?
>>> >>>
>>> >>> Thanks
>>> >>> Jay
>>> >>>
>>> >>> _______________________________________________
>>> >>> Chicago-talk mailing list
>>> >>> Chicago-talk at pm.org
>>> >>> http://mail.pm.org/mailman/listinfo/chicago-talk
>>> >>
>>> >>
>>> >>
>>> >> _______________________________________________
>>> >> Chicago-talk mailing list
>>> >> Chicago-talk at pm.org
>>> >> http://mail.pm.org/mailman/listinfo/chicago-talk
>>> >
>>> >
>>> >
>>> > _______________________________________________
>>> > Chicago-talk mailing list
>>> > Chicago-talk at pm.org
>>> > http://mail.pm.org/mailman/listinfo/chicago-talk
>>> _______________________________________________
>>> Chicago-talk mailing list
>>> Chicago-talk at pm.org
>>> http://mail.pm.org/mailman/listinfo/chicago-talk
>>>
>>
>>
>> _______________________________________________
>> Chicago-talk mailing list
>> Chicago-talk at pm.org
>> http://mail.pm.org/mailman/listinfo/chicago-talk
>>
>
>
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20120301/001d222d/attachment.html>