Lets say we're working on a database driven mod_perl application. We've got some tables in our database:

person: id, name, telephone_number, address, ...
person_jobs: person_id, company_id
companies: id, name, address, ...
company_products: product_id, company_id
products: id, name, description

Now, lets say on our main page we want to display a list of all the people in our database, and the company they work for. So, we create a sub called get_person_companies() (or something like that), which performs this task for us.

Then, later on, we need to display that same information on another page of our site, except we also want to display the persons phone number. Then we want a list of people, companies, and company products (contrived example, just to add to the complexity of generating the neccessary SQL).

How do you handle this? Create a new, nearly identical, sub for each set of differing data you need? Grab all the data and discard that which you don't need? Something better, I hope?

In the past I've taken the ad hoc route of just recycling the same sub to grab more and more data. I know that I'm unhappy with that approach, but I haven't been able to come up with an appealing alternative. I don't like the approach of writing a new sub for each set of data because:

  1. You duplicate a lot of code (although, I suppose, a lot of that is SQL),
  2. The thought of having two subs which perform such identical tasks bothers me, and
  3. I never know what to name the new subs (get_data_for_so_and_so_section() sounds wrong, but what is your alternative?).

Maybe I'm just being picky or something.

Anyway, Thanks for reading, and hopefully helping :-)

20040908 Edit by ysth: change title from Managing complexity

In reply to Handling near-redundant SQL queries 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.