Basically, you're describing a multi-tiered application with clearly defined responsibilities for each tier. While there are many ways to go about this, one of the most popular is to use an object persistence framework. One that is fairly easy to use is Class::DBI. This module allows you to create objects that model your database tables. You can read a short tutorial at perl.com, if you're interested.

It's important to remember, though, that this is not always a good fit for your code. Objects are heirarchical in nature; MySQL is not (this is sometimes referred to as the "object-relational impedance mismatch"). Class::DBI sidesteps this issue by ignoring it and leaving it up to the programmer to deal with. However, it's dead simple to use, easy to integrate with an existing database and I'm a big fan of it.

Tangram is another object persistence framework that attempts to properly map the heirarchical nature of objects to databases. The documentation describes various strategies that Tangram supports and the pros and cons of them. Here's an interesting quote from the documentation:

One of the paramount issues about mapping inheritance is how well the mapping supports polymorphism. Any Object-Oriented persistence facility that deserves its name needs to allow the retrieval of all the Fruits, and return a heterogeneous collection of Apples, Oranges and Bananas. Also, it must perform this operation in an efficient manner. In particular, polymorphic retrieval should not cost one SELECT per retrieved object.

The issues that it describes are ones that Class::DBI has unless the programmer takes care to work around this.

Though I understand that Tangram can be fitted to an existing database, this is probably not optimal. Instead, you define a class heirarchy and allow Tangram to design the database for you. It seems pretty clean and Tangram is now being actively developed once again, so if you see older information on it, be aware that it may be out of date.

For more information, check out http://poop.sourceforge.net/. This is an excellent introduction to the pros and cons of many Perl OO Persistence strategies. In particular, it will tell you whether or not MySQL is supported.

Update: I almost forgot to mention Pixie. From the docs:

  my $pixie = Pixie->new->connect(
    'dbi:mysql:dbname=test', 
    user => $user,
    pass => $pass);

  # Save an object
  my $cookie = $pixie->insert($some_object);

  undef($some_object);

  # Get it back
  my $some_object = $pixie->get($cookie);
[download]

Pixie basically says "forget about a framework, I just want to store an object." And that's what it does. Thus, you don't need to worry about designing the database. Simply use your objects and store and retrieve them as needed. No muss, no fuss. I've not used it, but it sounds very interesting.

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.