Atomic Values

Everything we store in a database should be atomic. An atomic value can not be broken down into smaller pieces. You may also hear “rule-of-one.”

Atomic: Caleb

Atomic: Curry

Not atomic: Caleb Curry

“Caleb Curry,” is not atomic because it is a value that stores both a first name and a last name.  This is not the biggest offense, as full name could still work, but it’s not always ideal.


It is important to first understand this concept before you plan your database because you will want to appropriately name attributes.

Whenever you can break up attributes into multiple attributes, you know they are not atomic:

Here you can see that the address attribute is not atomic. This is because address is an attribute that consists of multiple different attributes.

Where do we get the term “atomic?”

The term atomic comes from the idea that an atom is the smallest indivisible thing on this planet. Unfortunately, after the word “atomic” was created, we discovered that an atom could in fact be split. This means that atomic is probably not the best word to use, but many many people use it!

When is something atomic?

Something is atomic when we can think of it and use it as a complete whole! If you think of the atomic value “Caleb”, it is actually a bunch of letters used to create a word, but we do not go the extent of separating each letter as it’s own attribute…

First letter of first name = C

Second letter of first name = A…

This is ridiculous! We never think of names as a series of letters that need to be recorded separately.

As long as a value can be thought of as “a single thing,” then it can be considered atomic. A word is atomic because we think of it as one word, even though it consists of multiple letters. A picture is atomic because we think of it as one picture, even though it consists of multiple pixels. Even a phone number is considered atomic because we think of one phone number, even though it consists of an area code, an extension, and so forth!

Atomic Attributes and Atomic Values

We must realize that both the attributes and the values stored within the database must both be atomic. In the previous example, we used an address. The word “address” can easily be considered an atomic attribute because you think of one address as a whole, but the values are are not! The values of a single address attribute contain a street address, city, state, zip code, and can even include a country! We never want to store multiple values within a single column in a table. We must break this address up into 5 separate attributes, giving us the columns:

  • Street address
  • City
  • State
  • Zip code
  • Country

Following this one simple rule can help us design our database by showing us potential design errors! For example, look at this table:

Sale IDBuyer IDbought…..
3242032A new bicycle, 12 doughnuts, a shirt, a car,a girlfriend, pizza, a pet llama, a satchel

Here you can see that the “bought” column contains multiple values! Following the rule of one (atomic values only), we know that this is not going to work! We must fix this. I will be showing you how to fix this in the upcoming blogs about relationships, stay tight!

We will talk more about atomic values when we discuss database normal forms.

Rule of One

Each column should store only one indivisible (well…practically) fact. This also means that each table must only be for a single entity. Please keep in mind though, that tables and columns describe only categories of values…not each individual value…Each user does not get his or her own table, but each user get’s an individual row within a table:

Customer IDFirst nameLast namePhone number
1JohnJohnson123-234-3454
2BillyBillison321-432-5434
4CletusBilly123-764-2314
8CalebCurry800-435-1241
5SeeSquared542-123-1322

We get this pattern that travels upward in our database, each fact about an entity is stored in only one column, each column is stored in only one table, each row is unique and has values about only one entity, and each table is unique, once again storing information about one entity. Keep in mind though, that the opposite occurs when we travel down our database tree… A database can have multiple tables, each table can have multiple rows, and each row stores values for multiple columns:

Here you can see what the structure of a database looks like.

Not only must attributes store one thing, but these values must be all of one data type. You must specify a data type for each column and the rows can never break these columns rules.

Read the next blog to learn about relationships!

Conclusion

  • All values in a database must be atomic, in that they are thought of as one whole. Keep in mind though that we don’t want to go to the extreme by separating things into individual characters or whatever.
  • We want a perfect balance. The non atomic value “Caleb Curry” would make it much more difficult to sort by last name (because the database sees it as one value). The opposite extreme of having “C” “U” “R” “R” and “Y” would make it pretty complicated to sort by last name.
  • We want addresses atomic because then we can easily sort our database by street, city, or anything that we have as a column!
  • When a table breaks the atomic rule we can usually find problems in our table structure.
  • We want to try to follow the “rule of one” to create the best database.

Further Study:

Leave a Reply

Your email address will not be published. Required fields are marked *