Solved

Extra Custom Fields in a Database - combobox and radiobuttons

Posted on 2011-09-27
6
670 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 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
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…

623 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