Building the Right Database For Your Director Application

December 15, 1998
by Tommy Kiser

If you are anything like me, you did not start your career as a top-dollar, expert SQL programmer and gradually make your way into Director/Lingo programming. So you when you find out that your next CD-ROM project is going to involve a multi-million record, multi-table relational database, your first response is probably, "Guh," followed closely by, "Oh crap!" But never fear - it can be done, and, if you do it right, you might even impress yourself.

The first step is deciding what general approach to take. There are many various and sundry database Xtras out there, one of which may suit your purposes, but don't overlook the possibility of keeping it as simple as (or as complicated as) Director's internal list structures. There is much power in lists, property lists, lists of lists, property lists of lists, and so on and so forth. But there are many factors to consider when deciding what kind of database will work best for you.

How many records will you have, and how many fields per record?

If you have more than a few thousand records, or you have 500 records with 50 or 60 good-sized fields each (don't ask me why you would), then you had better start Xtra shopping. In a test movie, I have gotten 3-4 second search times on a 10,000-record database (7 fields each, one word or number in each field), even with complex Boolean queries (see next bullet for definition). Not too bad. But by the time you get up to 100,000 records with 6 or 8 fields a piece, you are talking about 25 seconds for a simple search, double that for a Boolean. And that is on my development machine with 96 mB of RAM...a low-end machine would not even be that forgiving, if it handled the query at all without crashing. The point is, Lingo lists are cool, but they are RAM and processor-speed intensive. If you are going to try and use lists for a large database, it is a good idea to run some bench tests (preferably on a machine that is representative of your lowest target platform) before you start your development process.

What kind of searches and/or sorting will be required

In case you are not familiar with that term, a Boolean query is a compound query using "AND" or "OR", like searching a pet database for an animal whose Type field = "dog" AND whose Breed field = "beagle." Boolean searches take longer than regular, single-criteria searches, and they take up more RAM. If you decide to create your database with Lingo lists, you will have to custom code for any complex searches that you will require, and the more complicated it gets, the slower it gets (as with almost anything in Lingo). Database Xtras are generally equipped with built in search functions that may be much faster and easier to use than their potential Lingo counterparts.

There is also the issue of sorting. If you need your database to be sorted in any way other than what the sort() function provides (which is not much), you may have to get into some creative coding. Most of the third-party database Xtras have built in sorting functions that are versatile enough to allow you to choose what field you want the sort to key on, what order you want the records sorted in, etc.

Is cross-platform capability important?

If not, then great. It doesn't really matter either way if you are going to go with Lingo lists. But if your project has to operate on both Mac and PC platforms, then be careful when choosing an Xtra. There are some Xtras out there that give you database functionality by adding ODBC, or Open DataBase Connectivity, to Director. This allows Director to "talk" to lots of different kinds of databases, but ODBC is Windows only, so don't look to an ODBC Xtra to be a cross-platform solution. V12 seems to be the champion of cross-platform database solutions in Director, but watch out...more about this in the "Choosing the Right Xtra" section.

This is probably as good a place as any to specify that I this article deals strictly with databases for installed-base (CD-ROM, Kiosk) delivery. If your database needs to be dynamically updateable from the web, you will want to take that into consideration when choosing your Xtra and/or deciding on a data importing strategy.

What form is your data in?

Sometimes your choice of database is necessitated by the format of the data you need to import. If your only source of data is a tab-delimited text file, then you need to make sure that your database code or software can import a tab-delimited text file. The field delimiter in a data file is the character that separates each piece of information. A comma-delimited file would look like this:

Dog,cat,mountain goat,fish,horse
Lion,tiger,bear,oh my

There is also a record delimiter, which, in the example above (and in most cases), is the RETURN character.

If you plan to build your database with Lingo, you want to stay away from Tab-delimited files, because director does not recognize the Tab character in a text field - if you import text containing Tabs into a Director text field, they will be converted to SPACE characters. Just have your data exported as a text file, delimited with commas or semicolons or tildes, or basically any other character that you are SURE will not be used within any of your fields. Most database programs should not have a problem with this...they will usually allow you many different options for data output, including various different delimiters and different file types. When you import the text file into Director, you can parse it into whatever format you like by setting the itemDelimiter property to whatever character you have used to delimit your text file, and then dealing with the text item by item and line by line.

If you are using an Xtra, just make sure that the one that you choose is able to import the format that your data is in, or that your data can be easily converted to a format that is compatible with the Xtra.

Choosing the Right Xtra

Now on to the ever-raging battle over which database Xtra is the best. I have first hand experience with Integration New Media's V12 DBE and Sight-n-Sound's Datagrip Xtras, so those are the ones I'll deal with here.

V12 and Datagrip both have sterling reputations for customer service and technical support. I will vouch for these reputations, as the technical support was speedy and accurate in my experiences with both companies. If you are fairly new to databases, this should be one of your main concerns, especially with a program like V12 where you have to build the database structure yourself through Lingo code. There are plenty of database Xtras out there, but if you decide to go with anything beyond the major players, make sure you do your Direct-L research to find out what experiences others have had with the software before you sink your cash into it.

Once you've found a few well-reputed Xtra manufacturers, it is time to start looking at what features you need. I'll start off by saying that if your database project needs to be cross-platform (Mac and Windows), then Datagrip is not for you. Datagrip is a popular Xtra that allows Director to communicate with a Microsoft Access database through SQL queries, which gives you a lot of power as far as searching and sorting are concerned. But because it uses MS Access, which is PC only, it is limited to the Windows platform.

V12 DBE is a cross-platform database Xtra that receives unending praise on Direct-L. I, however, have a different angle. I started a large project with V12, and it finally turned out that it was not able to do what I needed it to do, so I had to shift gears and completely redesign the entire project halfway through using the Datagrip Xtra.

For this project, I had to create a 4 table, 2.5 million record relational database and perform Boolean searches based on up to 4 different criteria at a time. For example, you had to be able to search for every horse whose name starts with "Mister," whose owner's last name is "Smith" or "Jones," and who resides in Texas. This information would have to be cross-referenced across multiple tables within the search. When I had the database built and I started testing these searches, V12 started breaking down. It would give incorrect results from time to time, but mostly it just crashed. In the end, though I will compliment the V12 technical support staff for offering as much help as they could, they finally informed me that, "Well, we have seen a few cases where that particular function just won't work, and I guess you are one of them." They said that they were trying to fix that problem in their next release, but I didn't have time to wait for that to come out. So, after much woeful contemplation, I switched to Datagrip.

To begin with, it was much easier to create the Access database than it was to build the V12 database from scratch. With Access, I just imported the data with the help of their handy wizard and had it all set up within an hour or two. With V12, it took a while to learn the code to actually create the structure of the database before I even got to the importing stage, and Access does all of this for you based on your data.

The imports also took only a matter of minutes with Access, whereas the importing process was hellishly slow with V12 (over an hour for a 5 mB text file). To top it all off, I had to cut out numerous indexes and get down to bare bones to get the V12 database down to 500 mB. The Access database I ended up with? 150 mB with all the indexes I wanted. (Ed: Check comments at end of document.)

So the moral of the story is, be careful. Set up simple test data to compare Xtras before you commit to one or another. Any decent database Xtra will have a downloadable demo that should allow you to get a feel for how well it will work for your project before you purchase it. If someone tells you that an Xtra is "good," find out the specs on their project. Just because XYZ Xtra worked with someone's 500-record student grade database doesn't mean its performance will stand up on your statewide license plate registry with 15 million records and two relational tables. And don't forget to consider the option of a Lingo-list database...it's always nice to be able to keep it within Director.

Sources:

Director Web's Direct-L Archives - All things Director
GJC Technical Ltd. - ODBC information

Editor comments: Bear in mind that the final size of your database is one critical factor. Internal Director structures, V12, DataGrip and FileFlex behave differently depending upon the size of the database. Use this as one of the deciding factors on which approach/tool to go with when building a database. A good approach is to evaluate the applicable solutions for the type of database you need and the ease of use of the database API as well as checking with folks who have done this before. Enjoy! - Zav