One rule of database design is to make your fields atomic. This article discusses what an atomic field is, and why it’s important.
What is an atom?
First, let’s talk about atoms. In physics, an atom is the smallest unit of ordinary matter. We used to think atoms could not be split into smaller parts. Of course, that changed with the atomic bomb, and then things got really crazy.
What is an atomic field?
In a database, an atomic field is a field that holds only atomic values, in other words it holds the smallest piece of useful information. If it holds multiple pieces of information, then it should be split and stored in multiple fields or multiple records.
Beware, the rule is not to split the field into the smallest piece of information possible, such as an individual character, or digit. Stored separately such information would not be useful, and like a split atom would make your database go crazy.
Examples of non-atomic fields
Storing a contact’s full name in one field would make the field non-atomic. Instead, the full name should be split into multiple fields. For example, you could split the full name into a first name field, a middle name field, a last name field, and possibly a suffix field if the person is a Jr. or Sr.
Table with Non-Atomic values
| Contact Name |
|---|
| William Jefferson Clinton |
| George W. Bush |
| Joseph R. Biden, Jr. |
Table with Atomic Values
| First Name | Middle Name | Last Name | Suffix |
|---|---|---|---|
| William | Jefferson | Clinton | |
| George | W. | Bush | |
| Joseph | R. | Biden | Jr. |
Storing a cell phone number and home phone number in one phone number field would make the field non-atomic. A better alternative is to store the home phone number in one field, and the cell phone number in another. Or you could store the two numbers in two separate records, and a new field for phone type.
Table with Non-Atomic Values
| Phone Number |
| 458-123-4567 cell 458-325-1234 home |
Table with Atomic Values
| Home | Cell |
|---|---|
| 458-325-1234 | 458-123-4567 |
Alternate table with Atomic Values
| Phone Type | Phone Number |
|---|---|
| Home | 458-325-1234 |
| Cell | 458-123-4567 |
Why must fields hold only atomic values?
Having each piece of valuable information in its own field, enables the database to its job. For example, if you stored the contact’s full name in one field, then it would be extremely difficult, if not impossible to have the database sort the list by last name. Similarly, if you stored all the contacts phone numbers in the same field, then it would be difficult to query the database to give you a list of contacts and their cell phones.
Setting up a database table to store atomic values in fields is one of the first rules of database design. TechMentors can help you when it’s time to design, or redesign your database. If you need help, please contact TechMentors.