[thelist] Database Schema Suggestions [Repost?]

[Burhan Khalid]

/Didn't see the first one in my inbox, so I'm resending it. Hopefully 
this doesn't show up twice/

Greetings All :

   I'm having a bit of a problem trying to sort out how to store 
information in a database that does not have a defined size. An item 
that can have /n/ amount of properties/attributes for example.

   What I'm trying to do is create an automatic form generation system 
for an event scheduling application. The administrator will create an 
event, and if that event requires registration, they would then create a 
registration form. The problem I'm having is how to structure the 
database so that I can store the form data ... specifically, any number 
of fields that the admin might want. It would be moot to limit the form 
to /x/ amount of fields.

   Thinking about it for a bit, I came up with one idea -- but every 
time I look at it, I find something else wrong with it (or situtations 
where it wouldn't work) and I'm hoping that some experienced database 
wizards can lend some advice.

   My idea was to create a table with all the possible fields that are 
allowed in a HTML form (text, checkbox, radiobutton, etc.) and give them 
each a unique id (in effect, a lookup table).  This would take care of 
storing the form element types.  Then I was going to have a field in the 
table that stores the form data (called elements) that would contain a 
comma-separated list from the lookup table. Something like this :

[ Lookup table ]
*----*----------*
| id | type     |
*----*----------*
| 1  | checkbox |
| 2  | text     |
| 3  | radio    |
*---------------*

[ Form table ]
*------------*
| elements   |
*------------*
| 2,1,3,1,2  |
*------------*

   However, I am also going to have a facility where the admin can 
choose fields that are mandatory and some basic checks (email, name, 
zipcode, etc.) but I don't know how I would store that information per 
field .. or where I would store it.  I thought about creating another 
table with possible validation types, and then doing the same comma-list 
in the form table for validation (which would be in the same order as 
the fields) but I have a feeling that there is a better way to do this.

   I thought about scraping the entire database approach and just 
creating text files with information for each form, and then storing a 
reference to that text file in the database, but I'm looking forward to 
any suggestions or ideas that you might have regarding this. Anyone have 
to deal with this before?  If such things matter, the target database is 
mysql.

Thanks,
-- 
Burhan Khalid
thelist[at]meidomus[dot]com
http://www.meidomus.com
-----------------------
"Documentation is like sex: when it is good,
  it is very, very good; and when it is bad,
  it is better than nothing."