Little Database

An instance of "When all you have is a hammer, everything looks like a nail". (See AllProblemsLookLikeNails). I've seen this pattern abused (and, truth be told, abused it myself) many times in the context of programming for "dynamic Web sites", which are almost always coupled to a database server such as Oracle, Sybase or Mysql.

Context

Your basic toolkit consists of a scripting language suited for embedding in Web content, such as PHP, Java (JSP), etc.; and a database containing business data, such as authentication information and demographic profiles for 'members' of your online service. You are writing "Web applications" to provide various services to these members.

Problem

Very often, you find yourself reusing a small data set over and over in your "pages"; a typical instance is the "postal address" part of a member sign-up form which has a drop-down list to select the user's country of origin. This will usually have from 20 to 150 or so country names, so embedding the list directly in your pages is unwieldy; in addition, you might be using the same list several times in the same page, e.g. once in a JavaScript client-side validation function and once for the actual HTML <CHOICE> tag.

Bad Solution

But wait - you have a database handy, so why not create a very simple table - one countryId column, one countryName column; you can then replace the literal list in your pages by code that fetches the list as a set of rows from the database and formats it appropriately for the context.

Resulting context

Your country data is now in a single place, which obeys OnceAndOnlyOnce; you have arguably obeyed DoTheSimplestThingThatCouldPossiblyWork, too. However, you have added one more table to your database, which is not always wise. Proliferation of "convenience" tables in the database will make headaches for the database administrators in the long run; also, database schema aren't easy to refactor. Database servers are also more suited to handling large sets of data which change a lot over time, as opposed to very small, essentially static sets of data. Overall, this pattern isn't entirely an appropriate division of responsibilities.

Antidote

Generally, you shouldn't use LittleDatabase unless the relevant data set really fits at least one further capability of relational databases : you want to be able to perform relational joins, for instance, or you need transactional protection for the data.

A more appropriate solution is the UseXml? pattern (http://www.xmlpatterns.com/UseXMLMain.shtml), more specifically XmlDatabase. Model your data set as XML, and use XSL transforms to format it appropriately. A good ContentManagementSystem (which you should have if you're maintaining a Web site with any significant amount of content) should make it easy for you to use this pattern. Or your database may provide the same functionality (Oracle and Sybase do).

Discussion

What about the chances of the application growing more and more complex over time? If you start out with a relational database, then it is easier to scale up rather than overhaul your API's from being XML-centric to relational ones. I just wish there was something lighter than MySQL, sort of a MS-Jet engine with a table browser/viewer/editor for open source.

I write Perl scripts that generate a set of pages based on a template and some input files. That way, the basic shape and style of the site is the same whereas I only have to worry about generating the content. If you think that http://usemod.com/cgi-bin/mb.pl?HtmlIsAssembler, you can see how this is almost like compiling a high level description of a page to the "native, low-level" static HTML file. A good example is http://sunir.org/sunir. -- SunirShah

I take it that only the country id ends up in the database? Any report program will also need access to the XML file to translate to country names when generating a report. (And it needs to know how to join entries from an XML file with rows from the database.) If you're using off-the-shelf software for generating reports, my guess is it's more likely to support a simple SQL join than an XML and relational data combination. --BrianSlesinsky

XML is for markup of documents, not for data. What you want, and get, is a data structure in memory, and the problem you are trying to solve is how to initialize that data structure, and to make any modifications to that if it changes. Therefore, you save it on a disk. An XML file might help if you get XML for free with lots of well-maintained tools, but then so might a Unix text file with one record per country, or a single-user "lightweight" database like BerkeleyDb?, HsqlDb, or SqLite. You could store your structure in your source code, your configuration management system, and the executable image in the file system (as a constant, or as a variable if your system supports image snapshots.) Finally, don't rule out your multi-user transactional database: by 2009 MySql and its competitors have become easier to maintain and may indeed be quite simple to operate for your purposes. There are many many ways to crack this nut, so choose the simplest thing that could possibly work, and the simplest is probably not XML.

CategoryLowEnd