For me one of the most exciting aspects of computer programming is problem solving. I have to continually re-evaluate the way in which data is interpreted and how data can be turned into information. Take, for example, the problem of storing an IP address in a database.
This is interesting because, on face value, it is not a problem. However, when it comes to actually creating a field in a table for the IP address then I am faced with a choice. Which data type should I use? Should I store the IP address as a number or as a string? It is, therefore, worth looking at the definition of an IP address.
What is an IP Address?
William Buchanan, in Mastering Networks, describes an IP (Internet Protocol) address as “an address which is used to identify a node on the Internet”. Garfinkel and Spafford (Practical Unix Security) explain that this address in a “set of 8-bit numbers” and give an example of an address as being 18.70.0.224. So, my dilemma is this – do I:
- create a single varchar (variable character) in which to store the whole address?
- create individual fields for each of the 4 8-bit numbers (or octets)?
This may seem an arbitrary decision but can have a major impact on the way that the data may be processed.
The Problem of IP Address Data Types
As Erin Toombs explains in How to Store an IP Address in a Database there are pros and cons with both methods of storage in the database. For example, if a varchar is used then the IP address will be stored in the database in the same format that the user will expect it to appear and exact searches are very easy.
However, searches on partial strings (such as looking for a octet containing 0) are much more difficult and may lead to unexpected results. Integer storage will mean searching for an octet will be easier but finding a whole IP address is more complicated.
The answer is, of course, to store both.
The Database View of an IP Address
Now, before any database designer becomes too apoplectic to read any further, let me explain that I am not proposing the duplication of any data. I am proposing the use of a database view. This view is simply a query stored on the database. In this case it will be used to turn the four integer octets into a single varchar IP address and the starting point is to create a table for the octets:
create table ip_octets (oct1 int, oct2 int, oct3 int, oct4 int);
Obviously this will also need appropriate indexing, but once the table has been created then the view can be constructed:
create view ip_address as select oct1, oct2, oct3, oct4, concat( oct1, ‘.’, oct2,’.’,oct3,’.’,oct4) as ip from ip_octets;
If I now populate fields oct1, oct2, oct3 and oct4 with 18, 70, 0 and 224 respectively then field ip in ip_address will automatically contain 18.70.0.224. The database will, therefore, contain both formats for the IP address storage with no extra disk space being used.