Atomic Database Values - How small is small enough?

We will, for example,normally separate first name and last name into two separate columns, as well as we would like to separate zip code from city. It all sounds quite natural.

The question of granularity, or atomic database values, is a very interesting and intriguing one. I advice you to read through this article, and at the bottom, there will be some exciting news for you :-)

But what do we really mean by atomic database values?

First, let us look at the term "atomic". It is probably meant to identify something "as small as possible". But as both you and I know, an atom is not the smallest possible item: An atom consists of protons, neutrons, and electrons. Again, are they the smallest particles? No, we have quarks, and a lot of other, strange particles (I guess you know more than me here :-).

Point is, we relate to atoms as the smallest particle for simplicity's sake. We really do not need to know about those elementary particles that together make up an atom. The atom itself is sufficiently elementary for us in almost all cases. It sure is for me :-)

So I have good news for you: Atomicity, or atomic database values, are pretty much what you define them to be. It depends on how you intend to use the information in that particular column.

Again; The definition of atomicity is actually yours, depending on your needs. Good, isn't it?

Actually, if your system is counting galaxies, and nothing deeper, I would call a galaxy an atomic value: That's the smallest part you are interested in.

That also goes for first and last name: If you don't need to treat them separately, consider the combination of both as an atomic value.

But: is it smart?

I don't think so: Split your information in as small parts as you think are convenient for your use...

I will illustrate it with a simple example. Let us take a look at my TRANSACTIONS table:

A nice little table. We will be playing with the TDATE (Transaction date) column. For simplicity, the table has just one row (so I don't have to write WHERE-clauses for this example. Sometimes I am a bit lazy...). The one row looks like this:

Not much cash there, but at least I have a date. I guess you also notice that none of the columns allow NULL values?

Now, the value in TDATE is clearly an atomic value? The answer is both yes and no. What is really behind a date? Let us explore, and since this example was created in Oracle, I will use Oracle-specific functionality to decompose and explore TDATE. Inside the DATE format lies a lot of information. Look at the date column more closely:

Is this really an atomic value? I am sure you have always thought it was ;-) But no, it is not: It contains the day, the month, and the year of the transaction: Three different items of significance!

But would you split that column into three separate columns? You probably wouldn't. I wouldn't. I would decide that the date concept is atomic ENOUGH for me, and you probably would, too.

So; the definition of what is atomic database values must be viewed in relation(!) to what is enough.

Just to take it one step further; look at what information I really can extract from that one column:

Now that's something! All elements have different values from the original. Of course, they are just a translation of numbers into characters, but they represent a new meaning.

And it is clearly not atomic database values, as we would like to think of the term "atomic".

But the DATE concept in Oracle offers much more. Look at this:

Now, from the TDATE column, I can actually derive the time of the transaction (I am working late, as you can tell).

So, the TDATE column does not hold an atomic value: it holds six different unit values (Actually seven; also milliseconds):

• Day
• Month
• 4-digit year
• 24-hour Time
• Minute
• Second

I will still claim that TDATE is an atomic value, even if it can be broken down into several elements. Why? Because it is sufficient for the use of the system!

This discussion on atomic database values may seem obvious to you, but it is not: It shows that you are in the driver's seat with regards to definitions of atomic database values and granularity. There are no exact, or rather, correct, definitions: It all depends on the business needs.

Now, that's a few reflections on granularity, or atomic database values, from the "atomic" side of it. Again: THINK. But let us think the other way: Could a column in a table contain a selection from one or more different table(s), with one or more columns, and still be considered atomic...

YES, according to the relational data model, it sure can! Imagine that!

Actually, the relational data model is so powerful, but vendors like Oracle, Microsoft, IBM and the rest (all trademarks, of course) have implemented it in a way that their products are lacking the full capability of the potential in the relational data model itself.

However, my article is now turning into a discussion about data types, or types, or domains, and how complex they can be. Actually, from the relational point of view, there has been said nothing about how complex a type can be, and quite deliberately so, too!

I will work more on this issue in the future, and let you know more about this. If you want to stay tuned and informed, I recommend that you subscribe to my free newsletter.

Return to Database Theory and Practice