For a long time I rolled my own subroutines based on reading the DBI manual over and over, but I've had a good experience with Class::DBI recently which gives you some object orientation. Some people swear on DBIx modules which I haven't tried.

It does seem a useful thing to put the SQL all in one place and be sure a table schema change won't fry you (i.e. you could access fields via a hash of column names). Often I have a routine for picking up one field as a scalar and one row as a hash. Stash that SQL away! Some people put sql into separate resource files or dictionaries, that's another way and it separates it even more. If you are just starting I'd say read the DBI manual a few times, otherwise you will not know the basis of most Perl database code.

To answer the question about Table A, the only thing I think is going to give you a massive performance gain is to use GROUP BY if you need to do a big request. Otherwise get a row at a time and read fields off as you like. The other way is to use an object-oriented module and if I remember correctly Class::DBI lets you tell which fields must always be fetched and which are to be done lazily.

Another thing to consider is object persistence (maybe overkill for you now) which is provided as mentioned by some frameworks, SPOPS comes to mind. These things usually let you forget about loading and saving objects it is all transparent, theoretically. Anyway what you use and how much you want these things to stay in the background is a matter of style.

Anyway the DB layout has little to do with the UI as far as I can see, it is based on the information design or underlying metaphors you have set out in advance. In fact I wouldn't do UI design until after that stage. For the UI anyway I would recommend HTML::Template because it has always been great for me.

I'd like to mention also SQLite which seems to be useful and is a fast SQL db in a file.

Anyway listening to your needs I would probably recommend at this stage learning the DBI and rolling a couple utility routines using it, and as far as UI goes use a template system.

In reply to Re: Mixing Mysql and Perl by mattr
in thread Mixing Mysql and Perl by SavannahLion

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.