[Chicago-talk] creating relational keys

[Jay Strauss]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20120301/e51f4d4d/attachment.html>