[thelist] Database Schema Suggestions [Repost?]
Burhan Khalid
thelist at meidomus.com
Fri Nov 14 14:26:24 CST 2003
/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."
More information about the thelist
mailing list