[Chicago-talk] creating relational keys

[Jay Strauss]

thanks Chris !

We solved it essentially the same way it looks like.

Jay

On Thu, Mar 1, 2012 at 1:57 PM, Chris Hamilton <cjhamil at gmail.com> wrote:

> 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
>
> _______________________________________________
> 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/20419a24/attachment-0001.html>