I would extend to Rohits idea a little to allow granularity and the ability to create dynamic search queries by creating a table structure for each field type so that I can store and index the field data natively.
Any queries can be slow on this type of dynamic structure, but if each form data is bound by a common header id then you can even create a view for a form data, by using a Table Select for each field.. Select (select Value from IntTable where ID = @HeaderID and FieldID = @FieldID) Age, (select value from strTable where Id = @HeaderiD and FieldID = @FieldID) Surname from FormHeaderData where HeaderID = @HeaderID The above queries can all be dynamically generated by you on each site and rebuilt as Stored Procedures for speed. On Wed, Mar 6, 2013 at 12:45 PM, russell <[email protected]> wrote: > Rohit’s method is like one I use that allows users to “add fields”.**** > > ** ** > > Users add records to an existing table. The program manages these records > (so it appears to users) as if they are fields.**** > > The field types are important if you allow users to store data that will > not fit into a fixed length field. **** > > ** ** > > I allow three data types: Text, FileNames and Memo. The first two are > single lines of text of any length and the third handles multi-line data.* > *** > > All three types are stored as BLOBs in a Firebird db file.**** > > ** ** > > A “con” is these fields are not easily searched … but I do not need this > ability.**** > > ** ** > > Best regards,**** > > Russell**** > > ** ** > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Rohit Gupta > *Sent:* Wednesday, 6 March 2013 11:49 a.m. > *To:* NZ Borland Developers Group - Delphi List > *Cc:* List NZ Borland Developers Group - Delphi > *Subject:* Re: [DUG] Runtime user defined fields**** > > ** ** > > I would consider having a generic table where you soft-add the fields > > Ref_Num : autoinc > Owner_Num : int64 > Field_Name : string (16) > Field_Type : integer // ord(TFieldType) > Field_Data : string (255) // store everything as string but the type > above tells you what it is > > This way you dont alter the database. You dont have to worry about > indexes etc. Everyones database is the same. Yet you can search for data > for a particular field name. I am actually using this structure in a three > projects. > > Regards > > Rohit > > On 6/03/2013 10:41 a.m., Steve Peacocke wrote:**** > > Hi everyone,**** > > ** ** > > I've been away from programming for a while so please excuse the**** > > seemingly simple question.**** > > ** ** > > I'm designing a user interface for a new application and wondered if**** > > there was a generally accepted way that this "should" be done.**** > > ** ** > > The application will allow end users to edit their forms allowing them**** > > to add, move, and remove fields at runtime. I'm using MySQL but I was**** > > wondering if there was an accepted way of adding and changing fields**** > > to a database.**** > > ** ** > > I know I can use SQL for this but I also don't want to make every**** > > application unable to be managed or upgraded. Perhaps I can ensure**** > > that the 3-4 permanent fields are never able to be altered (ID, Name,**** > > etc) but allow the user (through a suitable user-friendly interface)**** > > to go crazy adding such fields as My_favourite_colour, Rating,**** > > Priority, Notes, etc. etc.**** > > ** ** > > I'm able to give a suitable user-friendly interface to allow them to**** > > physically alter the Delphi form at runtime, but wondered about the**** > > "most appropriate way" to modify the fields to the database.**** > > ** ** > > 1. I could simply add fields using SQL's ALTER TABLE**** > > 2. I could add another table with fields such as TableName, FieldName,**** > > Type, etc.. and add to that**** > > 3. Some other spiffy way that you people will suggest that I haven't**** > > even thought of yet.**** > > ** ** > > Any suggestions?**** > > ** ** > > ** ** > > Steve Peacocke**** > > +64 220 612-611**** > > http://www.nzorient.co.nz**** > > _______________________________________________**** > > NZ Borland Developers Group - Delphi mailing list**** > > Post: [email protected]**** > > Admin: http://delphi.org.nz/mailman/listinfo/delphi**** > > Unsubscribe: send an email to [email protected] with > Subject: unsubscribe**** > > ** ** > > ** ** > > __________ Information from ESET Endpoint Antivirus, version of virus > signature database 8081 (20130305) __________**** > > ** ** > > The message was checked by ESET Endpoint Antivirus.**** > > ** ** > > http://www.eset.com**** > > ** ** > > ** ** > > ** ** > > ** ** > > ** ** > > -- > Regards > > *Rohit Gupta* > B.E. Elec., M.E., Mem IEEE, Member IET > Technical Director > Computer Fanatics Ltd > > *Tel *4892280 > *Fax *4892290 > *Web *www.cfl.co.nz**** > ------------------------------ > > This email and any attachments contain information, which is confidential > and may be subject to legal privilege and copyright. If you are not the > intended recipient, you must not use, distribute or copy this email or > attachments. If you have received this in error, please notify us > immediately by return email and then delete this email and any attachments. > **** > > _______________________________________________ > NZ Borland Developers Group - Delphi mailing list > Post: [email protected] > Admin: http://delphi.org.nz/mailman/listinfo/delphi > Unsubscribe: send an email to [email protected] with > Subject: unsubscribe > -- Kyley Harris Harris Software +64-21-671-821
_______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: [email protected] Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [email protected] with Subject: unsubscribe
