Solved

Extra Custom Fields in a Database - combobox and radiobuttons

Posted on 2011-09-27
6
664 Views
Last Modified: 2012-05-12
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


0
Comment
Question by:starhu
  • 3
  • 3
6 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 36714217
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
 

Author Comment

by:starhu
ID: 36720023
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 36720447
Ah sorry forgot you were using Delphi, which I have no knowledge of.  Sorry can't help with that.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:starhu
ID: 36813111
The 36720023 comment doesn't belong here -> I wanted to post to another issue
0
 

Author Comment

by:starhu
ID: 36813116
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 36813157
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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question