Mulching Monks,

Is there any good reason to prefer a Perl or a Mysql solution to the following issue:

A user is going to submit multiple option choices via a web form. Say one through N. They will be submitting these choices regularly, and I want to keep track of what their choices were each time. They will be stored in a Mysql database, but I see two possiblities:

1. Give this update event a unique identifier, create an 'options' table with columns 'update_id' and 'option', and store a separate update_id - option_id pair for each option chosen. That is, if the user selects 5 options on Monday, ther ewill be 5 rows inserted into the table, each with Monday's update id. To retireve the options selected on Monday, I select all rows that have Monday's update id.

2. Pre-agglomerate the options in Perl into a formatted string, such as 1|2|5|8|13|55, and store that in a single inserted row. To retrieve Monday's selections, I select the single row with Monday's update id and do a split.

There are about 1,000 users, and each will be making about 6 updates per year, making about 5 to 10 option selections per update. And I have to keep the data for 7 years. So version 1 could end up with ~1/4-million rows, where as version 2 will have ~1/5 ~ 1/10th that, but require the extra Perl.

Speed of retrieval is not critial, per se, but neither will it look good if my system chunks along like a tug boat. So, which is likely to be faster?

Thanks.




Forget that fear of gravity,
Get a little savagery in your life.

In reply to Perl or Mysql to store multiple choices? by punch_card_don

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.