Tip

Serial ID fields

Many databases allow you to create serial ID fields--fields that increments by a number every time you add a record. When properly applied, serial ID fields are particularly useful because they uniquely identify a record. These fields are used as primary and secondary key fields to match one database table (or file, depending upon the database used) to another. Some database developers swear by serial ID fields; others swear at them.

The problem with serial ID fields is that they are automatically generated and if the database engine loses the sequence you end up orphaning your data and perhaps making the data useless. Many databases offer a reserialization feature that will correct serial fields to fill in missing numbers, and to reset the next number in the sequence. If you make the mistake of reserializing your serial ID field, woe is you.

The other problem about serial ID fields is that the data they contain tells you nothing about the record, its time of creation, who the record belongs to, and so on. Therefore, many developers have adopted schemes to replace serial fields with key fields that are unique and offer some guidance as to what the purpose of the record is. For example, in a hospital database it makes more sense to create a field of, say, 8 characters that are created from the first four letters of the persons last name, and the first four letters of their first name. Then Barrie Sosinsky becomes "sosibarr" in that key

    Requires Free Membership to View

field. In the unlikely event that Barrice Sosin checked into that same hospital, subtracting a letter and adding a number, as in "sosibar1", breaks the tie.

When you have a high performance database engine like FoxPro, you can do even more sophisticated key fields. One well-known New Jersey consultancy creates long key fields from a combination of the serial data and time of creation, as well as the user name of the creator. Since each time of the system clock is unique, each record must be unique. And the benefit of this approach is that you can parse the key field to order records or to draw out records created by a particular individual.

So the next time you are constructing a database, ask yourself if you want to trade the relative simplicity of implementing your key fields as serial ID fields, or whether your data set would benefit from a different approach.

About the Author

Barrie Sosinsky (barries@killerapps.com)is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training, and technical documentation.

For More Information


This was first published in April 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.