It really depends on your data. It appears that you take the first two fields (I'll call them text1 and text2) and use one as the key to a hash, pushing the other onto an array which is the value of that key, and which is the key and which goes onto its array depends on whether a field later in the line is greater than some parameter you supply.

So you end up with a hash of arrays. Since your hash keys are unique, they could be one field in your database. But we don't know what ends up in your arrays. Do they all have the same length? Are they consistent in what they hold? To map your hash to a SQL table in a one-to-one fashion, the hash values all need to be structured the same.

As an example, let's say your file looks like this:

A ||| Adam ||| 1,2,3,4,5
B ||| Bob  ||| 1,2,3,4,5
B ||| Brian ||| 1,2,3,4,5
Carl ||| C ||| 3,2,1,4,5
Bruce ||| B ||| 3,2,1,4,5
C ||| Cheryl ||| 1,2,3,4,5

[download]

And you call your sub with a $p value that causes the single-letter values to be used as the keys, with the longer texts pushed onto them as arrays. You'll end up with this hash:

%hash = ( A => [ 'Adam' ],
          B => [ 'Bob', 'Brian', 'Bruce' ],
          C => [ 'Carl', 'Cheryl' ]);

[download]

Here, your hash data doesn't "line up" with columns in a database table, since you have different numbers of fields for each one. So in this case, you'd either need to concatenate your arrays into a single "name" field with a delimiter, or have multiple records for each key. One of the following layouts:

letter     names
------------------------
A          Adam
B          Bob;;Brian;;Bruce
C          Carl;;Cheryl

letter     name
------------------------
A          Adam
B          Bob
B          Brian
B          Bruce
C          Carl
C          Cheryl

[download]

Generally, the second method is best, because it simplifies searches and saves the cost of splitting on the delimiter every time you need a name. But it depends a lot on what your actual data looks like. Your first step should be to think about your data and design a table for it, and then figure out how to insert the values. It doesn't matter that you're putting it in a hash now; think about how it should be structured in terms of set columns and go from there.

Aaron B.
My Woefully Neglected Blog, where I occasionally mention Perl.

In reply to Re^3: storing a large hash in a database by aaron_baugher
in thread storing a large hash in a database by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.