2 Replies Latest reply on Jul 12, 2011 12:13 PM by philmodjunk

    What is the best practice for storing text data?



      What is the best practice for storing text data?


      I've been building a complex system for months and I have a number of people that use the system. I currently have text such as names and addresses using Title Case formatting but I'm realizing that there is room for error with names such as McDougal and such. Then I started looking at how other databases handle this and usually, all data is UPPERCASE. I'm looking at converting all the data and every input field to store the data as UPPERCASE then allowing me the ability to convert it to anything else later. Is this good practice for programming or am I just asking for a headache? What is the best procedure to do this?

        • 1. Re: What is the best practice for storing text data?

          It all depends on your personal needs and desires actually. Personally I think you are building a future headache. Don't worry so much about the format of the text as it is stored, in fact all lower case I feel is best. You can worry about how it is displayed when you actually display the text, a database is for storage. Get my meaning?  =)

          • 2. Re: What is the best practice for storing text data?

            Making everything uppercase strikes me as a brute force solution that doesn't really handle capitalization issues with names like McDougal as there isn't really a consistent rule you can use to apply proper capitalization to the names once you have stripped this out. It's an easy approach to use, so if you and your users are satisfied with seeing the names as all uppercase, then it's an option to consider, but personally, I find all uppercase names are a tiny bit harder to read at a glance and convey an "institutional feel" to your reports that isn't always the best effect to produce when your users/clients/employer reads reports that list these names.

            I've played around a bit with the following compromise approach implemented as an auto-enter calculation. It can't fix every capitalization error, but it fixes a few of the most glaring ones:

            If ( Exact ( Upper ( self ) ; self ) or Exact ( Lower ( self ) ; self ) ; Proper ( Self ) ; Self )

            This calculation detects uncapitalized text and all upper case text and attempts to fix the error by applying proper case to capitalize the first letter of each word in the text field. If capitalization was applied, then the text format is not modified.