[Chicago-talk] creating relational keys

[tiger peng]

Do you have to use Perl? Let relational keys done by relational database may be easier.
BTW, 

As I know, in US, some zip codes may cross cities and even state boarder. It cannot be simply represented in tree structure nor self-referenced table.



________________________________
 From: Chris Hamilton <cjhamil at gmail.com>
To: Chicago.pm chatter <chicago-talk at pm.org> 
Sent: Thursday, March 1, 2012 2:08 PM
Subject: Re: [Chicago-talk] creating relational keys
 
Yeah, looks that way. :)

It's too bad you're forced to use such a model, it's not going to make
for very lovely queries, I don't imagine.  Good luck!

-Chris

On Thu, Mar 1, 2012 at 2:06 PM, Jay Strauss <me at heyjay.com> wrote:
> 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
>
>
>
> _______________________________________________
> 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/584e8652/attachment-0001.html>