Tuesday, October 9, 2007

Data Always Goes In A Database, Right?

Not necessarily.

I have worked on a number of software products where data (usually metadata) is stored in text or binary files. The code opens the file, reads from the file and parses the text (if necessary) into the structures used internally by the code. Over the years, the question has been asked more than once: why don't you put this data into a database?

There are a number of reasons not to use a database. First, all the data needs to be in memory for performance, and there is no advantage to keeping parts of it out on the disk. Second, the data is usually not appropriate for being "table-ized." That is, the data is complex and tree-structured rather than row-structured. I know that one can always flatten data into multiple tables with the appropriate keys and joins, but parsers built with tools such as Yacc and Lex will always be faster than doing multiple database joins and reads.

A third reason is that upgrading the data in the database requires scripts to add or modify tables or columns (deletes not being done to reduce the potential for referential integrity problems). Parsers can recognize a version number and do an upgrade-on-read, which doesn't require another program to run to perform the upgrade.

Fourth, files are easier for end-users to handle. For example, if a user needs to send some of his data to tech support, it's much easier for him to package up one or more files, rather than having to run some database dump utility and send the dump file. There is also no issue with (once having that database dump) the possibility that tech support does not have the particular version of the database software that the user is running.

A fifth reason (for text files at least), is that in a pinch the file can be opened and edited in a text editor if there are problems with it. With a database you have to run the appropriate database client software, then tease out the table relationships to understand where changes need to be made.

When all you know how to use is a hammer, everything looks like a nail.