[Chicago-talk] creating relational keys

[Chris Hamilton]

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