[Chicago-talk] creating relational keys

Chris Hamilton cjhamil at gmail.com
Thu Mar 1 11:57:11 PST 2012


Assuming that all keys at a given depth of the hash structure are
unique (probably a big assumption with city names in a state), the
attached script example should probably do what you need (assuming I'm
correctly understanding your problem and you're trying to figure out
how to iterate through the hash structure and do the necessary inserts
with the correct parentIDs).  If I'm wrong about which problem you're
having I apologize, but hopefully this provides some insight.

Thanks,
-Chris

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: testload.pl
Type: application/octet-stream
Size: 1238 bytes
Desc: not available
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20120301/587dc105/attachment.obj>


More information about the Chicago-talk mailing list