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 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 (firstname.lastname@example.org)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
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our Database Design gurus are waiting to answer your toughest questions.