What do I do with my data?

At this point in time there are two broad categories of approaches you can use to manage your data:

• Relational
• Non-relational

Since one is defined in terms of the other I'll start with relational.

Like the name suggests, relational data are items that can be related to one another through the use of a key value. For example, your government issued an identifier number to you that is used to identify you. This number is then linked to your name, where you were, born, etc. It is a key to reaching that information.

If you think of a table of information, with rows running horizontally across the page and columns intersecting the rows vertically, running down the page, the key value identifies the row. So, for information about person you might have something like this:

Key	FirstName	LastName
100	John	Smith
101	Fred	Brown
...	...	...
174	Zachary	Miller

Each key identifies a row, and each row has columns of information. Then, in another table where you store, say, order information, you do not have to enter John Smith's personal information every time you create an order for him. The data exists in one place. Then, if you find out that he spells his name "Jon" and not "John" you can change it in once place.

There are other advantages to the model, but this is the crux of it. Information only has to be entered once, and then related to other information.

A relational database management system (RDBMS) is software that allows you manipulate this type of information easily.

Non-relational is a grab bag of different approaches that do not include necessarily the idea of a key value (or at least not in a separate tables sort of way). Generally a non-relational data store will store all of the information for any one thing all together, all at once. For example, an order would be stored all on one line, with the customer information repeated over and over for each order they are linked to. You wind up storing the information more than once for certain items and you have a lot of work to do if you want to update that information or use it to link to information that is held elsewhere.

Storing information in a spreadsheet often winds up being non-relational but in more modern systems there are ways to make the data relate to other data. The lack of management tools, however, will generally drive you towards a management system.

If the data you are storing is VERY simple and small in size you can save time and money by just storing it into a text file that you update by hand. For anything beyond that you should consider more sophisticated software to help you with the process of storing and updating the information.

Weighing the pros and cons of the various different packages is a very involved process. Think through what your needs will be and then invest the time in some research. Make sure you base your decision on your needs and not on what some marketing tells you your needs are.

Make a techgnostic choice for your database needs and you will achieve your goals effectively and efficiently.

What kind of a database do I need?

So, we covered the need for one. Chances are you need one. But which one? First we have to think a bit.

There are some questions to consider the answers to:

• What kind of data am I storing? Is it just text and numbers, or do I have to store files or picture? Do I have to store documents?
  
• How much data am I storing? Do I have a few dozen customers and a couple of suppliers, or do I have hundreds of both? Do I have historical information I need to put in, and how much of it do I have?
  
• How many people need to get at it? Is this just for me, or do other people need to get at it also?
  
• What other requirements do I have? Speed, costs, etc. Does the system have to lightning quick or can you wait a second or two? Do you have a budget to spend on this, both for software and somebody to handle it?
  

There are several broad categories of alternatives.

• If the amount of data is small, simple and textual you could get by with a text file. An XML file can easily hold a fair amount of structured data and does not require very powerful software.
• If the amount of data is larger and maybe a bit harder to manage you might need a spreadsheet like Open Office Calc, Google Doc or Microsoft Excel.
• If your data needs are larger you start to get into actual "database" software, of which there are many different options from free to very expensive. The choices stretch out a great distance, but include smaller ones like Microsoft Access or FileMaker or ones that will scale out as needed like MySQL, PostreSQL or Microsoft SQL Server (various editions).
  

In each case the number of people need to access the data will drive what you wind up with. Think about your needs before buying what people are selling and come back to determine what kind you need. Answering these questions will help you be techgnostic in your choice.

Do I need a database?

"Oh, obviously you need a database."

What?

What is a database, you ask? All you know is that it lives on the computer somewhere and keeps track of things. Why can't you just keep your paper and files? Or are you already using a computer? "Look, see - I have it all in a spreadsheet!".

The person who told you of your need for a database rolls his/her eyes and groans and says "THAT is not a database!"

Confusion reigns.

So, what is it?

Strictly speaking, ANY collection of data is a database. Your paper and files? That's a database. A bunch of index cards in a box? Database. That spreadsheet of yours. Also a database. Any one of many different software products with the word "database" in their name? Yeah, database. Really, a database is an organized body of related information. Period. The exact mechanics of it are where people get hung up.

Honestly, the person who recommended a database to you most likely meant that you would benefit from a "database management system" (DBMS) such as has been around for many years, specifically designed to manage your information. Even though your stack of paper is technically a database how long does it take you to find a very specific piece of data out of it, say, an invoice from a particular vendor? When the pile is small it is trivial. As the pile grows it becomes non-trivial.

You've moved from a small pile / number of cards / number of spreadsheets. What happens when someone else needs to get at the same information? Any more than a couple of people cannot effectively search through your pile. And when the information must be updated what do you do? Mark up the original? Attach sticky notes? Put the file on a shared drive and hope for the best? And if you want to know some kind of aggregate information like "how much money do I owe all people with outstanding invoices?" you now have a much larger task on your hands. That is where a DBMS really comes into its own.

A DBMS has been designed, from the beginning, to efficiently store your information, with methods to capture, store, analyze and create outputs based on the information stored within. They are at the core of much of the efficiency that is attributed to computer technology.

For anybody who is doing anything in their lives that requires capturing information, storing it for future use, sifting and analyzing it and creating output such as reports or simply answers to questions about the data the need for a computerized database is clear.

However, just like the question of "do I need a computer" (most often "yes") is followed by "what kind of computer do I need?" there are some questions you have to ask yourself before you start down the path of using a DBMS. As the blog title implies we will cover this in a techgnostic fashion in the next post.

For a sneak peek, the questions will be along the lines of:

• What kind of data am I storing?
• How much data am I storing?
• How many people need to get at it?
• What other requirements do I have?

That will be followed by another post wherein some of the more popular alternatives are considered, as well as some less popular choices. Come to think of it, this might become a series of posts. Stay tuned.

Object Relational Mapping

I saw a demonstration last night of object relational mapping (ORM) tools. The idea behind them is that object oriented programming (OOP) and relational databases (RDBMS) model the world in two totally different ways. OOP tries to express everything in terms of behavior and properties, whereas RDBMS focus on efficient data storage, and do not support the kind of behavior that OOP provides. You can program in a RDBMS (with stored procedures, or functions) and you can store data in objects. Both approaches have their pros and cons. At the moment, OOP is the dominant way to write software (Java and .NET languages sit on top of huge libraries of objects) and RDBMS is the dominant way to store data (Oracle, SQL Server, etc.).

Programmers being programmers, clever people have put together some ways to allow the OOP people to have their cake and eat it too. They can program against objects, and then have a whole bunch of backend code handle the dirty details of getting data out of and back into the RDBMS. Of course, there is no such thing as a free lunch, so there is an overhead associated with this.

I could see how the approach would allow you to cleanly separate out your business objects (e.g., customers, orders, etc.) from the nitty gritty of the data storage (e.g., the exact SQL statements to retrieve or insert data into a database) and insulate your business logic from future changes in data storage technology. Some frameworks go one step further and define a domain specific language (DSL) to allow you to express your business problem solution in something that resembles normal English (COBOL-esque?).

In a large number of cases, there is significant investment in existing code, written in such a way that you cannot simply switch approaches on a whim. When something like ORM comes along there needs to be a cost-benefit analysis (CBA) of whether or not it is worth it to make the switch. The software developers hear this and hear "blah blah blah", knowing that the suits just want to kill their fun. If they are serious enough about their fun they will go to a startup company that is doing greenfield development where they can indulge their latest cutting edge ideas.

Ironically, in three to five years something else will come along, and if they have totally bought into whatever the new thing NOW is (for example, ORM) they will decry any attempt to move away from it, having written tens of thousands of lines of code using it.

I guess it is the fate of all software development to eventually become obsolete and fall by the wayside, but in some niches you can survive for a very long time until that CBA finally tips the scales over, and the maintenance payments to whichever vendor or ability to find programming talent to keep your AS400 / COBOL / PowerBuilder payroll system alive become too difficult.

I suspect I will run a small pilot project using an ORM framework, to see what benefit there is, and to show that we are "keeping up", but for now I think it is going to be one of those things that simply does not pay off enough to warrant a wholesale shift.