Atomic Database Values - How small is small enough?In database design, we strive to break down information into atomic database values, or single items.
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.
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):
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.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.