Solved

Extra Custom Fields in a Database - combobox and radiobuttons

Posted on 2011-09-27
6
659 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
Comment Utility
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
Comment Utility
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
Comment Utility
Ah sorry forgot you were using Delphi, which I have no knowledge of.  Sorry can't help with that.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

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

Author Comment

by:starhu
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now