Database Tips: Use the Atomic Principle

Early physicists and chemists discovered it is much easier to join atoms together than to split them apart. Remember this when designing your database.

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.

That's an, er, elemental explanation of the atomic principle. So that your boss doesn't go nuclear on you, think through how this would play out for data entry. Typically, users enter data via a form. Maybe it's a Web form or maybe it's part of an application.

If the user has to type in a first name and type in a last name, then simply splitting these into two entry fields makes no additional work for the user. And people are accustomed to splitting up the name this way.

People typically do not like typing in the street number and the street in different fields. They aren't accustomed to it. You might have a good reason for forcing them to do so, but if not then this particular atomization is probably not a good idea.

Thinking again of what people are accustomed to, what about entering a date? The database will want to store it by most significant digits first. Thus, 11MAR2014 becomes 2014-03-11. Sure, you can have the UI save the date format to what the database needs. But what about different date customs? Prior to a certain horrible day, 95% of the world's population would have read 9-11 to mean the 9th of November. But in the USA, dates are done backwards from how everyone else does them (except in aerospace, military, police, much of engineering, and most internationally-oriented companies).

To avoid problems with date entry, atomize! Set up dropdown menus. You will need three of them: one each for the year, month, and day. Your form can have these in whatever order would most make sense to the users.

Whatever you do, ALWAYS use letters for the month. Do not use numbers. Why?

1. People commonly select the wrong numbers for June and July when they have numbers in a form.
2. You want to make it clear that this is the month and not the day. No matter in which order you put the day and month, you will confuse someone--unless the month is a three letter representation.

The atomic principle can greatly help you make your database more flexible and scalable. But think out the implications of how each data field would need to be implemented in a user form.

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.