user defined dynamically created fields

[seadog]

This might be a long one so bear with me please.

I have a client who wants me to develop a database for him. Lets call it an events database. He know he needs fields like date, time, location, cost, but he thinks later on he might want to add other fields. These fields might be text, or numbers. He doesn't want to go back into the code or DB to make changes. He wants an webbased admin interface to create these new fields. Also these new fields may need to be drop down lists in some cases.

I will then need to have users enter data and store the data for these dynamic fields when they enter events into the database. Does anyone have an algorithm for solving this type of issue?

Thank You,
Seadog

[mourisj]

This might indeed be heavy to implement: Just think that those fields need to appear in a form, so you need to manage this, too.

Without trying this out thoroughly, I would do somthing of this kind (I dont give the complete syntax, just the idea how I would do):

Table user_defined_fields
field_id
field_type (number, char, ...)
field_name
field_has_predef_values
...

Table predef_values
pdef_id
field_id
presentation_type (means list-box, radio-button, ...)
pdef_name
pdef_value
pdef_order (of item in the list)
pdef_is_selected (preselected/checked values)
...

Table forms
form_id
field_id
field_position
...

Table userdef_field_values
field_id
pdef_id
char_value
num_value
date_value
event_id
...

user_defined_fields contzains the definitions of any user-defined field.
If there need to be predefined values (for a list-box, etc), they are set in predef_values.
User input come into userdef_field_values: the pdef_id if there are predefined values, in char_value if it is a character field, in num_value if it is a number, and so forth.
In forms you define to which form the fields needs to appear, and the position (sequence) of the field in the form.
You can extend this last one to make really nice forms with these user-defined fields, but I don't want to think about how to achieve this.

This was quick and dirty, I hope you see what I mean.

Have fun,

JJ Mouris