When it comes to storing formatted fields in a database, the adage "store raw, display pretty", usually holds true. In most cases, raw values are the most conducive for working with in the database, allowing them to be queried, sorted, compared, and what-have-you. Yet, there are times that you may want to leave in special characters, where they are essential to formatting, such as HTML markup. In today's blog, we'll explore both options with examples using Navicat Premium.
Parsing Out Special Characters
Consider a field that stores phone numbers. Just in North America, a phone number can be represented in many different formats, including "(800) 555-1212", "800-555-1212" "800 555-1212", or "8005551212". To store short pieces of variable data like phone numbers, it's usually best to strip out special (non-numeric) characters at the application layer before storing to the database. The application would also be responsible for presenting phone numbers in the predetermined display format. If you're concerned that all of this parsing and reformatting of data will place unnecessary strain on the server, rest assured that the processor overhead of formatting a phone number is trivial, taking far less than a microsecond in real-time.
Data Type Considerations
Some people think that numeric data like phone numbers lend themselves to a numeric data type such as int or bigint. That being said, most DBAs choose the char or varchar type over numeric ones, as non numeric characters can be valid in phone numbers. A prime example being + as a replacement for 00 at the start of international numbers.
For evidence of this practice, look no further than the Sakila Sample Database. There, you'll find a phone number column in the address table. Here it is in the Table Designer of Navicat Premium:
Here, the phone field is given a length of 20 in order to accommodate a variety of phone numbers. A quick glance at the table contents shows the varying phone number lengths:
The good thing about varchar fields is that, if you ever needed increase the column's capacity, you could do that easily enough using an ALTER TABLE statement, or simply by changing the Length property in Navicat.
Preserving Formatting of Longer Fields
For longer fields that contain formatted of free form user input, like descriptions, you may find it preferable to store them in a varchar or text column with all of the special characters included because there would be no way to reformat them for displaying later.
Viewing Free Form Content in Navicat
Content that spans more than one line can be difficult to work with because the typical Grid view only shows one row per record:
Navicat offers a couple of ways to view longer fields:
Form View
The Form View allows you to view, update, insert, or delete data as a form, in which the current record is displayed by field name and its value. Form View also provides pop-up menus with the following additional functions:
- set the field value as Null/Empty String
- use current field value as a filter
- format form view
Text Editing
Navicat provides Text/Hex/Image/Web drop-down to view and edit TEXT/BLOB/BFile/HTML field content. To enable viewing/editing of a data type, select the type from the drop-down and toggle it to the ON position. In the case of TEXT, you will see an editor appear at the bottom of the Table Grid:
Conclusion
In today's blog, we learned how to store formatted data using Navicat Premium.
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
