Database Tips: Use The Singularity Principle

Use each field of a database for one purpose and only one purpose. Do not use one field for several different things.

Sometimes, a field has been used some of the time for one purpose and is then shanghaied into another duty. Avoid this. Create a separate field for each role. Then there is no confusion about what a specific field is used for.

Yes, you can have several variations of a basic field. The USPS, for example, has Address 1, Address 2, and Address 3. Each has a specific purpose.

An example of double usage would be a field for apt/suite. This is a bad idea. People live in an apartment and work in a suite. So one is a residential address and the other is a business address. These should be separate fields.

The singularity principle is similar to the atomic principle. The atomic principle requires that you use several individual fields rather than a single field that combines several together. It can be difficult, and sometimes impossible, to extract the required data element from a combined field. However if you have individual fields, you can always find ways to join them together.

An example of this would be to create fields for firstname and lastname rather than have a combined field for name. With separate fields, you can sort by lastname, and select data based on firstname.

A combined field would make one, the other, or both difficult to do. While you are at it, include fields for title and initials, a common name (or nick-name), and maiden name. You may even want to include fields such as alias1, alias 2, dba1, and dba2.

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.