Having tackled a similar project before using MySQL, let me outline how I accomplished this kind of functionality, and some thoughts about representation:

From a logical view, it looked like this:

• Each form object contains an ordered list of field objects
• Each field object has a name, and the name/id of a widget, flags (is this field required?, etc). It optionally has some configuration info for the widget (like what options to display for a dropdown list, etc).

Widgets are things like: textbox, textarea, dropdown, checkbox array, radio button group. They are implemented as modules with a common interface, and you will need to build a library of widgets to choose from. They must know how to retrieve (pack) the appropriate data from param into some internal representation (for storage in a table perhaps?), and how to output their HTML (unpack) given an existing representation to redisplay to the browser, or a fresh, blank form. And if these are email-forms (it sounds that way), they should know how to unpack their data to an email-friendly format as well as HTML (so each widget should have associated output templates using your favorite templating module).

From an implementation standpoint, widgets do most of the work. The request to display a form gets mostly delegated to several fields, which each delegate most of their work to a widget. Same with delegating the request to submit a form. The form and field objects just do a bit of assemblage and pass their results back up the chain. Write a robust collection of these widgets, keep the interfaces clean, and you will be able to do a lot with this framework. A big "gotcha" to watch out for, though, is that a "widget" may be composed of several logical HTML form elements. In your interface, don't make the assumption that one widget == exactly one param().

There are a few things that make this non-trivial to represent in a purely relational SQL system. First, the list of fields has to be ordered. You can do this in relational SQL, but it's icky. Second, fields like dropdown lists and radio buttons need extra info associated with them, generally in the form of a variable-length list. Again, possible in relational SQL, but not so much fun to model and implement. They are probably very easy in an object-relational DBMS, but as my only experience is (unfortunately) with MySQL, I can't give an opinion.

Because you mention adding people to distribution lists, I'll assume that these are all email forms and that you don't need to store the submissions. If that's the case, lucky you. You don't have to worry about making tables with dynamically created/removed/edited columns. But let's forget about database design for a second and move on to...

A more generic suggestion: How would you want to have this data available internally within Perl? It might be a structure kinda like this:

%forms = (
  foo_request_form => [
    { 
      field => "Username", 
      type => "text", 
      required => 1
    },
    {
      field => "# of Foos needed",
      type => "numeric",
      required => 1
    },
    { 
      field => "Reason",
      type => "dropdown",
      required => 0,
      opts => [ "None", "Need more Foos", "Want to waste paper" ]
    }
  ],
  
  change_access_form => [ ... ],
  new_directory_request => [ ... ],
  ...
);
[download]

This may sound absurd, but a good place to start your coding would be to build a prototype using Data::Dumper (or Storable or any other serialization module) as your database -- just read and write this big complex object to a file somewhere. The reason I suggest this is because I find it much much easier to write code to manipulate a native hierarchical Perl structure than to think about fetching tables, relations, foreign keys, etc. Honestly, how hard is it to rearrange items in a Perl array? Compare that with the "real database" code necessary to do the same thing, which already had you a little concerned in your post. This will give you an easy way to try lots of different data design ideas very quickly -- the beauty of Perl. So build up some routines to perform the appropriate add/delete/edit tasks to this data structure, as well as the routines to display and submit a form. And if things go well and the app is fast enough, you can just keep Data::Dumper around, add some locking to the database file, and you're set!

If Data::Dumper is not up to your performance/integrity standards, I would not suggest using MySQL for all the reasons mentioned above. On the other hand, a data structure like this wouldn't as bad to implement as an XML document. XML already preserves the order of its elements, and variable-length lists are no problem. Also consider an object-relational DBMS based on the object relationships listed at the top of this increasingly-long post ;)

Anyway, those are my thoughts on the subject. Hope it's not more than you bargained for! If you're interested in more specifics about how I implemented this kinda stuff in my project, let me know and I can delve into more detail over email or something. If this is not at all helpful, well, at least I exercised my fingers. At the very least, consider the suggestion to build a rapid prototype using a Data::Dumper database, especially if you haven't yet set in stone the DB schema / data structure layout.

Cheers,

blokhead

---

---

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.