This isn't strictly a perl question, it's more a db design question--but I'm going to be using perl eventually to get, insert and manipulate this data. If this is inappropriate for PM, please delete this writeup.

Consider:

I'm storing the information about who ran what report when in a single table (this is a legacy). One of the fields is an ID that is generated using a uid, a timestamp and a varying list of whitespace delimited strings that denote various report options.

For instance, one report id might be something like "uid333 200309091230 H J Krm 3 2003-6-30" while another may be "uid333 200309091231 A L foobar 4". So you can see that while these ids have something in common, they also have a lot that's not in common. The other columns in the table are identical. The id is what is used to determine if the client has run the report previously within a specified time limit, and they are charged less for the subsequent runnings.

For example, if I run the report that corresponds to "uid 200309091230 H J Krm 3 2003-6-30" in the morning, and again after lunch, the report running script compares the ids (generated partially from the report options) and if there is a match, compares timestamps (data in another column) and charges accordingly.

My question is this: does anyone have any thoughts about how to store the report options in such a way that eliminates this cumbersome report id string? How does one deal with data that is loosely similar?

AH

As a p.s., the # options for a report are not predictable. In other words, it's not enough to know that I ran a XYZ report at 2pm. You must know that I ran an XYZ report with the values 1,1,2,3,5,8. Which is entirely different than the XYZ report with values 5,8,13 or just 21,baz.

p.p.s. the report running script currently does some chopping of the report id in various places and some pattern matching to determine a match.

In reply to Suggestions on storing loosely similar data by alienhuman

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.