Should You Go Beyond Relational Databases?

A wonderful article by Martin Kleppmann from thinkvitamin:
http://thinkvitamin.com/code/should-you-go-beyond-relational-databases/

File System Storage Versus BLOB Objects

Document Management Systems nowadays are heading towards storing all documents as BLOB objects in the database. You rarely find any server side applications that deal with documents as files.

Whether its a... Document, Content, Bug, Project, Knowledge Management System, they're all implementing document storage using BLOB objects.

Why it that then?

Well...

1. The first advantage of having everything stored in the database is the ease of backups and restorations. You no longer need to backup the database then backup your document's directory, then make sure they are synchronized.

2. The second advantage is that the documents are stored the same way on all platforms and partitions. Whether its FAT32, NTFS, or any other filesystem, you can store them all in the same way.

3. The third advantage is, you have full control of versioning documents, storing interesting information like diffs, dates, users performing on them.

4. The fourth advantage is that third party applications that connect to the database need only to have the database port available. No need to provide extra ftp access to acess the documents.

Thats it. Bottom line...

If you're building some kind of server application, consider a Database File System.

SQL Statement Shortcuts

What always bothered me when writing SQL SELECT Statements is handling long table names, and having to reference a field by its table's name to avoid ambiguous errors.

Take this for example, three tables: infobase, infobase_labels, and infobase_users. And let's imagine that those three tables each have a field named 'ID'.

So a simple select statement will go like that:

SELECT infobase.ID, infobase.NAME AS INFO_NAME, infobase_labels.NAME AS LABEL_NAME, PHONE FROM infobase_labels, infobase_users, infobase WHERE infobase_labels.ID=infobase.LABEL_ID and infobase_users.ID=infobase.USER_ID AND infobase.ID=5;

Okay, 238 characters of unreadable sql code.

Whats next? Okay, till here you have two options:
1. Live with it.
2. Find a simpler way.

I have been using option 1 for the last 7 years. Until, option two showed up ...

... the usage of name aliasing for table names to make a query simpler, now this I love, simple alias each table with a 2 letter name, and use it instead of the table name:

SELECT _IB.ID, _IB.NAME AS INFO_NAME, _IBL.NAME AS LABEL_NAME, PHONE FROM infobase_labels AS _IBL, infobase_users AS _IBU, infobase AS _IB WHERE _IBL.ID=_IB.LABEL_ID and _IBU.ID=_IB.USER_ID AND _IB.ID=5;

Okay, this makes them 205 characters long.

For me the latter is much more readable, shorted and easier to write.

Tidying the SQL statement a bit:
SELECT
_IB.ID,
_IB.NAME AS INFO_NAME,
_IBL.NAME AS LABEL_NAME,
PHONE
FROM
infobase_labels AS _IBL,
infobase_users AS _IBU,
infobase AS _IB
WHERE
_IBL.ID=_IB.LABEL_ID
AND _IBU.ID=_IB.USER_ID
AND _IB.ID=5;

Okay thats it for today. Quite a long post, but I like it ; )

Say Hi to Derby.

MySQL, Postgres and now Derby... a new database that adds up to the competition. This time from IBM directly to the opensource community and under the wonderful Apache License.

Now this database is amazingly amazing, it supports transactions, its java based, it has a small footprint (2 Megabytes), it supports full encryption storage, it supports multiple access methods including SQL and low level API calls.

What I like about Derby the most is that it started as a commercial database (known as IBM Cloudscape) and then got released to opensource. This guarantees the best of both worlds; a commercial quality database with full opensource transparency and community support.

My only problem with projects that start as opensource is that you rarely find quality efforts put into them unless they are being sponsored by a big company or are provided with donations.

On the other hand, commercial products, if not thriving for quality, they risk a chance of losing their competition and their business. Not only quality software, but quality documentation and quality support and quality *. With a lot of exceptions here and there, I still believe that commercial products have a greater probability of meeting high quality standards.