Extra Custom Fields in a Database - combobox and radiobuttons

Hello,

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)
Address: VarChar(80)
Phone 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).

thank you


starhuAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
johanntagleConnect With a Mentor Commented:
I don't have a tested solution but it should be as simple as:

1.  Add a CustomTableId int column in CustomTable
2.  Create your CustomTable_Values table with a CustomTableId column that has a foreign key referring to CustomTable.CustomTableId.  This will allow you to link between the two tables using integer id's which is faster than comparing strings
3.  Maybe even add a position column in CustomTable_Values so the user can set consistent sorting of the possible values.


0
 
starhuAuthor Commented:
Hello,

I attached the picture. I am very sad about this :-(

When browsing among templates how do I know which template supports sub menus? That would be very cumbersome to download , install and try all templates the customer likes.


 menu setting
0
 
johanntagleCommented:
Ah sorry forgot you were using Delphi, which I have no knowledge of.  Sorry can't help with that.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
starhuAuthor Commented:
The 36720023 comment doesn't belong here -> I wanted to post to another issue
0
 
starhuAuthor Commented:
johanntagle:"Ah sorry forgot you were using Delphi, which I have no knowledge of. "

It doesn't matter I need an sql solution. I will try what you said - to be frank I thought of the same solution, but I was wondering if there is a better, more elagant solution , or something that would make it more usable
0
 
johanntagleCommented:
Ok your "misplaced" comment got me confused, thinking you need a full SQL+Delphi solutiion.  Anyway I don't think there's any solution more straightforward than what we're both thinking of.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.