Choosing datatypes

Table of contents:

Choosing correct data types for your data is vital for building a robust web applications. While some suggestions may sound obvious for seasoned developers following these rules may save beginners hours of debugging time.

Use date fields to store date values, store numeric data in numeric fields. Some functionality like search for specific dates or sorting will not work right if you store dates or numeric data in text fields.

What datatypes to use with care

1. Fixed length character types like CHAR or NCHAR in SQL Server

Use this datatype only for fixed length data like two character US state or five digits US zip code. What happens when you, for instance, store passwords in CHAR field? If your password is shorter than field length it will be padded with spaces to fill the whole field. Needless to say your login page won't work as password entered by the user won't match one stored in the database.

2. Long text fields like TEXT in SQL Server or MySQL.

Because TEXT values can be extremely long you might encounter some constraints in using them. For instance, you won't be able to sort your data by TEXT column. In MySQL sorting will be limited by first max_sort_length bytes, while SQL Server will throw an error.

SQL Server, at least SQL Server 2008, will not let you run a query like this (where 'textfield' is a TEXT column):

select * from tablename where textfield='some text'

You can only run LIKE queries against TEXT fields in SQL Server. This means you cannot use TEXT fields to store usernames or passwords. The best bet for regular text data is varchar.

Key columns

What are key columns and why do I need them? The primary key of a relational table uniquely identifies each record in the table. This can be a single column or a combination of columns.

In your application primary key is required for those tables that need Edit, Delete or View functionality, need to work with images or provide functionality like Print/Export selected records.

The best option is to use autoincrement field as a primary key. Your database will take of generating the proper key value every time you add a new record. In MS Access such field type is AutoNumber, in SQL Server use INT IDENTITY field, in MySQL use INT AUTO_INCREMENT field.

"Bad" characters in table/field names

As a rule of thumb only use alphanumeric characters naming your tables and fields. The first character in the name must be letter. Avoid spaces, dashes, hyphens, slashes etc. This makes things much easier from programming point of view. If you need to make fields more readable use underscores (order_date) or camel case (OrderDate). Avoid names like Order_Date or "Order Date". Whatever naming convention you choose make sure you use it through the whole project.

Here are some database specific tips:

  • Oracle: recommendation is to create table and field names in upper case. Use underscores to separate words.
  • MySQL: table names - lower case only, will save you some headache while moving databases from Windows to Linux and vice versa. Field names can me mixed case.
  • SQL Server: the recommended way is to use CamelCase while naming tables and fields.

Make sure to check your database guide for more in-depth info.

Aliases in SQL queries

SQL aliases are used to give a database table, or a column in a table, a temporary name. The main idea is to make SQL statements more readable and also to avoid ambiguity.

Some users make a mistakes using SQL queries aliases as field labels. Here is an example of such a query:

name as `Customer Name`,
orderdate as `Order Date`
discount as `Discount Rate%`
from orders

This approach is incorrect. First, you will lose all field project settings if you decide to change the alias. Second, you won't be able to build multi-language applications. If you need assign field labels proceed to 'Label Editor' on Miscellaneous screen.

When do you need aliases in SQL query?

1. When you use calculated fields in your query

Select [ID],
([EPACity] + [EPAHighway])/2 as AvgEpa
From [Cars]

2. When you need to use the same field more than once in SQL query

select ...
segment as segment1,
segment as segment2
from mytable

3. When you join another table that has field with the same name

select orders.id, order_details.id as order_details_id 
from orders o
inner join order_details od
on o.id=od.orderid

Runner specific notes

There are some additional tips that only apply to applications built with PHPRunner, ASPRunnerPro or ASPRunner.NET

  • key columns must appear in SQL Query
  • do not use aliases for key columns
  • use long text fields like memo in MS Access or mediumblob in MySQL to store image/file info. Each uploaded file info requires about 300-400 characters to store all file info
  • when you use encryption make sure that field provides enough space to save encrypted value. Encrypted value can be 2-3 times longer than original one. You also need to remember that only text fields can be encrypted. You cannot encrypt date or numeric fields.