How to Choose Data Types

Top  Previous  Next


Dates and Times

LONG is the usual data type for both date and time values where external compatibility is not an issue. This allows you to use Clarion Standard Date and Time arithmetic. It is also a more efficient storage mechanism, since a LONG is 4 bytes of storage while mm/dd/yy in a STRING is 8 bytes.

The DATE and TIME data types are useful only for external compatibility with Btrieve tables that already use them. There is no other advantage to DATE and TIME data types than the compatibility issue, since the values they contain are always internally converted to LONG before any math is done on them.

For xBase tables, you generally use STRING data types for date storage, because STRING is the actual data storage that all the xBase systems use. See the specific database driver's documentation for further information on this issue, because your choice can be affected by whether the table already exists or your program needs to create it.


ZIP Codes

The DECIMAL data type is very good for zip codes because it's a packed decimal format -- a 9 digit ZIP+4 in a DECIMAL is 5 bytes of storage while 9 digit zip in a STRING is 10 bytes. This kind of storage savings is a real consideration when you're setting up a large database. Since there is no math to perform on ZIP codes, storage is generally a larger consideration than performance.


Phone Numbers

The DECIMAL data type is also very good for non-international phone numbers, for the same reasons as ZIP codes. Since you're dealing only with phone numbers in your own country, you should be able to define the exact number of digits and format to display. For U.S. numbers, you can store the area code separately from the phone number -- use a SHORT for the area code (3 digits in 2 bytes) and a LONG for the phone number (7 digits in 4 bytes) -- and achieve the same storage as a single DECIMAL(10,0) (10 digits in 6 bytes).

If your program must deal with international phone numbers, the best data type is a STRING, because the most common method of indicating the country code is with a plus sign (+). For example, +44 (0)800 555 1212 indicates country code 44 (the United Kingdom). You should make the STRING at least 19 characters, since the number of digits in the number can vary from country to country, and even within separate sections of the same country.


"Customer" Numbers

"Customer" Number is defined for this discussion as: any internal number in your program used primarily as the linking field between Parent and Child files.

LONG is the most common data type used for internal numbering for linking purposes. It is very efficient for both storage (4 bytes) and execution (it is one of the base data types used internally by the Clarion libraries – see Base Types in the Language Reference). Any KEY based on a single LONG field is very efficient and small on the disk, since it will require fewer key node splits than a KEY based on a longer STRING (like the "customer" name).



The best data type for any field that will store money values is DECIMAL. Using DECIMAL provides the most efficient storage, since it is a packed-decimal format. It also provides Binary Coded Decimal (BCD) math functionality, which means that calculations are executed in Base 10 instead of Binary (as it would if you use REAL). Using BCD math eliminates the rounding and significant digit problems that you can encounter when you use any of the floating point data types (REAL, SREAL, BFLOAT4, BFLOAT 8).