What do I mean by dynamic text form fields? Let's say I have a table Contacts that stores typical contact information for a Contact Management system.  However, on my add/edit Contact screen, I want to give the user (in this case, a sales rep) the ability to add 3 extra "dynamic" fields that can be used to collect non-standard information.

Instead of a standard field, such as firstname, whose (non-editable) label is on the form as "First Name", next to a textbox allowing the entry of a first name value, the "dynamic" fields would allow the sales rep to create whatever label they wanted and then store the value that relates to that label.  See form mock-up image below.

Let's say that all the sales reps like to keep "extracurricular" information about their contacts in order to strike up more casual conversations.  But each sales rep has their own personal style. Sales rep Bob always keeps up with their hobbies, favorite sports team, and birthday.  Sales rep Jane always keeps up with their spouse's name, children's names, and their alma matter.  Sales rep Bill always keeps up with their hometown, favorite food type, and if they have any pets.

Since making all 9 (and potentially a lot more than 9 for a company with more than 3 sales reps) of these "fields" part of the Contact table would just bloat the database schema with a lot of fields that usually null, we'll need to find another solution.

Following database normalization rules (specifically 1st Normal Form), an obvious solution would be to create a child table such as ContactExtraInfo, with an ID field, a Contact ID field (foreign key), a key field, and a value field.

Or, since we're not collecting a lot of information per contact, an alternative would be to use a large text field (in MSSQL that would be varchar or nvarchar) full of delimited text.  This prevents you from having to create a child table JUST to store 3 dynamic key/value pairs.

In our example, let's say we limit the sales rep to 20 characters for the field name, and 60 characters for the field value for 3 "dynamic" fields.  Storing this in a delimited string would require no more than 305 characters -- 300 for the data, 3 for the name/value delimiter, 2 for the per-field delimiter.  In MSSQL this would be varchar(305) or nvarchar(305).  In other words, if we were using "|" (pipe) to seperate out each field, and "`" (backtick/grave accent) to separate out the name and value, the data might look like:

Hobbies`Golf and Gardening|Fav. Sports Team`Titans|Birthday`October 17th

I use the pipe and backtick because they are unlikely to be part of the actual data -- at least in the U.S. (and as such, I filter them out of the data before saving to the DB).  You can use any delimiter that fits your purpose or scope.

What does VB.Net have to do with this?  I'm glad you asked.  In order for this concept to be useful, we'll need an easy way of converting the delimited string that is stored in the DB into a more usable collection of data and vice versa.  The .Net Collection class I recommend is the Hashtable class.  The Hashtable class allows for easy storage, manipulation, and retrieval of key/value pairs.

When data is inside a Hashtable object, accessing a value by it's key (name) is simple:

MyHashtable("Hobbies") 'Returns the String "Golf and Gardening"

Adding key/value pairs is just as easy:

MyHashtable.Add("Fav. Sports Team", "Titans")

To remove a key/value pair:

MyHashtable.Remove("Hobbies")

Looping (a.k.a. iterating) through all the hashtable's keys & values is easy as well:

For Each sKey As String In MyHashtable.Keys
    Console.WriteLine("Key: " & sKey & " Value: " & MyHashtable(sKey))
Next

Finally, here are the functions that will convert a Hastable to a String, and a String to a Hashtable:

  'Converts hashtable into string
  Public Shared Function HashtableToString(ByVal ht As Hashtable) As String

    Dim sb As New StringBuilder

    If Not ht Is Nothing Then
      For Each sKey As String In ht.Keys
        sb.Append(sKey)
        sb.Append("`")
        sb.Append(ht(sKey).ToString())
        sb.Append("|")
      Next

      Return sb.ToString().TrimEnd("|")
    Else
      Return ""
    End If

  End Function

  'Converts string into hashtable
  Public Shared Function StringToHashtable(ByVal s As String) As Hashtable

    Dim ht As New Hashtable

    If s.Trim.Length > 0 Then
      Dim aKeyValues As String()
      Dim aPairs As String() = s.Split("|")

      For i As Integer = 0 To aPairs.Length - 1
        aKeyValues = aPairs(i).Split("`")
        ht.Add(aKeyValues(0), aKeyValues(1))
      Next
    End If

    Return ht

  End Function

Even if you don't have a specific need for these type of "dynamic" text form fields, this code is useful for any need you have for storing a reasonable amount of key/value pairs in a DB record without creating the overhead of a child table.

Note: I only advocate breaking database normalization rules when the decision to do so is intentional (and documented).