Crystal Reports: Reserved Words

Don’t use reserved words in your database design. When designing a database, we try to use meaningful names for our table and field names. But sometimes a database has reserved some words for special functions, so it is best to avoid these. But which ones?

The SQL manual should list the obvious ones like SELECT, FROM, WHERE and GROUP.

Different databases have different reserved words. We first met this problem when we migrated an Access 97 application to SQL server. The Access table had a field called DESC which is a reserved word in SQL indicating a descending sort sequence.

The solution in SQL was to use the field name surrounded by a delimiter: table.[DESC] worked fine.

Crystal sits between you and the SQL needed to retrieve a recordset. We recently found a database table called FILE and Crystal Reports with SQL did not work. Our solution was to create a view called vwFILE of the table. The view had all the original fields, but as vwFILE wasn’t a reserved word, Crystal Reports could generate SQL to retrieve data.

Many programmers use a prefix on all tables and fields. Each table could start with tbThisTable and tbThatTable. Each view could use vwMyView. In some databases we have seen the table name abbreviated into a prefix for each field. So the Customer table has cuName, cuAddress1 as fields, while the Supplier table has suName and suAddress1.

This approach doesn’t work well with Crystal Reports and Smart linking. But then Smart linking often creates links where they are not needed.

Some other tips also apply. You can ignore these at your peril, or more likely at the peril of your frustrated and confused users.

Industry jargon

Take care you don't use specialized words from your industry, unless those words are of specific use in the database. If using those words, ensure they are used correctly per your industry standards.

An example would be a fieldname "grounding" for an electrical construction firm's database. This might be a useful field, as such firms are often involved in grounding projects. But in common parlance, this word is frequently misused to mean "bonding" and the confusion is responsible for more than a few deaths and fires every year.

In this particular case, the database designer would be better off using "earthing" for the field name if the intention is to cover actual grounding because the definition of grounding per the National Electrical Code is "connected to the earth."

Spelling

There's no excuse for spelling errors in a database. While some people think it doesn't matter, because the database is "hidden" from the typical report recipient, these errors do have some downsides:

• Unprofessionalism. This sloppiness advertises your lack of professional attitude.

• Confusion. A person trying to analyze, repair, update, import, or export a database or its data might not be able to find a field or value due to a spelling error.

• Propagation. The errors have a way of spreading.

Spaces, characters, capitalizations

Don't ever put spaces in database fieldnames; that's simply asking for trouble. Special characters may cause issues for exporting, importing, translation, calculation, and other operations. Capitalizations add a needless layer of complexity.

Embedded information

Don't embed information in field names or in the format or type of data you plan to populate the database with. This adds needless complication, results in errors, and introduces inconsistencies over time. Keep this simple.

An example of complication is "coding" company names so that the best customers have the first three letters of their names capitalized. This system will break down, and it also doesn't allow actually using this information in an intelligent way. If you need this kind of information, make a field such as "topclients" or "clientgrade" and then you can sort or filter on that information.

Combination

Combining data that should be separated is a classic mistake. For example, using the field "name" is a bad database design technique. It's better to use firstname and lastname so you can parse things out as needed. You also eliminate several potential errors this way. Think about it, and see if you can name at least three.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.