|Syntactic Confectionery Delight|
Migrating database field values rules from Perl code to DBby punch_card_don (Curate)
|on Feb 12, 2007 at 15:07 UTC||Need Help??|
punch_card_don has asked for the wisdom of the Perl Monks concerning the following question:
Consider a typical database and a db management system written in Perl. Table "Users" has fields Name, Join_Date, and "User_Type". On the "Create New User" screen, the Name field that accepts free-text input, the Join_Date input is a date drop-down menu with year, month day, and the User-Type input is a drop-down with the three possible user types.
This arrangement means that part of the database specification - the allowable values of fields - is encoded into the html input page. That is, the database spec is disjoited, partly held in the definitions of the columns, and partly held remotely in other files.
I'd like to "correct" this by including all database specs in the database. I imagine a Perl-cgi generated input page instead of the static-html one. It will draw the necessary allowable-values information right from the database and dynamically generate the input form based on that.
The most likely place to store such information that I've come up with so far is in the COMMENTS field for columns (MYSQL). Then they can be retrieved with a SHOW FULL.
Then, in my Perl script, I'll have to have a CASE-type construct. If this field is defined as a free-text input, then output a text-box input. If it's defined as a limited-value drop down, get the values and generate a SELECT.
But, before I embark on this, I'm wondering what others have done with this.
Forget that fear of gravity,
Get a little savagery in your life.