I have a software written in Delphi that uses MySql tables. I want to let the users create their own custom fields.
Let's say the customer table is the following:
Name : VarChar(40)
I have a CustomTable table with the following fields:
`field_name` varchar(100) default NULL COMMENT 'contains the physical field name',
`user_field_name` varchar(100) default NULL COMMENT 'the field caption named by the user',
`data_type` int(11) default NULL COMMENT '0=editbox,1=memo,2=integer,3=decimal,4=date,5=timestamp,6=checkbox,7=combobox,8=radio button',
`field_length` varchar(30) default NULL
So in my software the user creates a new record in the Custom Field form, writes in the name and caption of the field, chooses the type e.g. date and when clicks on Create button I will do the slq in the background so as to create the field in the customer table.
Also in my software I will create a corresponding data edit depending on the type e.g. if checkbox then I will create a TCheckBox component and set the checked=1, the unchecked=0
The only problem I have is with combobox and radiobuttons because they can contain as many values as the user wants.
For example if the user creates a listbox component Cities he can add New York, Rome,Paris, Hong Kong values. And perhaps a week later he would delete Rome and add New Orleans and Dubai.
So my question is where do you think I should store these extra values for comboboxes and radiobuttons?
I suppose I should make another table as CustomTable_Values.
Do you have a tested solution for this? (I speak of database design, not of end user interface).