Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Extra Custom Fields in a Database - combobox and radiobuttons

Posted on 2011-09-27
6
Medium Priority
?
674 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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