|P is for Practical|
Handling near-redundant SQL queriesby Anonymous Monk
|on Aug 31, 2004 at 22:59 UTC||Need Help??|
Anonymous Monk has asked for the
wisdom of the Perl Monks concerning the following question:
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:
Maybe I'm just being picky or something.
Anyway, Thanks for reading, and hopefully helping :-)
20040908 Edit by ysth: change title from Managing complexity