Friday, March 16, 2007

NightHawk Database

NightHawk uses the latest in database technology to securely store your data. I thought I'd take a little time and talk about our data architecture.

Heart of the 'Hawk

When I was designing NightHawk, one of my first decisions was our database. Databases have come a long way since Now Up-to-Date's and Now Contact's databases were written in the early '90s. The NUD and NC databases are flat-file databases designed for high speed and small memory footprint. While they were excellent back in "The Day", requirements change. For NightHawk, I wanted a database that:
  • is an embedded SQL database
  • is FAST for data retrieval and reasonably quick for storing new data
  • has a reasonable and predictable memory footprint
  • has a reasonable startup time
  • can store millions of records
  • runs on Macintosh, Windows, and Linux
  • is open source (or at least I can get the source code if need be)
  • integrates with other tools for data mapping, data analysis, and data interchange
Wow, that's a tall order! From my survey of the field, there are only a handful of databases that meet those requirements, including SQLite, HSQLDB, H2, and Derby. Of those, I like SQLite and HSQLDB the most. Although I'd used SQLite on my proof-of-concept for NightHawk and was familiar with its source, I opted to use HSQLDB mainly because it is better supported by other tools that we're using.

Object Mapping

NightHawk is an object-oriented application, so my goal was to store my data as objects in a database. Unfortunately there's a small (yet significant) disconnect in mapping objects into SQL. To make things easier, I decided to use an ORM ("Object Relationship Mapping") layer. All an ORM does is provide an object-oriented interface to the SQL database. The ORM I chose is called "Hibernate", and it does a surprisingly good job. Using Hibernate I create a file that describes my data objects and then have Hibernate generate the SQL for me. This is similar to Apple's CoreData in Tiger, except Hibernate works on Macintosh + Windows + Linux, and runs on 10.3 and later. Hibernate + HSQLDB is also pretty performant. On my MacBook Pro, NightHawk loads a 100,000 record contact list in less than a second. That should be fast enough for most people's needs!

Pick Your Database

Hibernate also has another useful (and required) benefit. Since Hibernate generates the SQL for me, and since Hibernate supports a wide range of SQL databases, I am free to try different databases with NightHawk. Although NightHawk is currently configured to use HSQLDB, it is relatively easy for me to use a different database like PostgreSQL or MySQL or Oracle. I'm planning on trying some of the other databases as we implement the pro-level server since they may have better performance characteristics for extremely large databases.

Syncing

When I designed the database schemas for NightHawk, I decided to take a different approach than we did with NUDC. (Sidebar: a database schema is the definition of the fields in a database table, and the relationship between tables. For example, the NightHawk database has tables for contacts, addresses, etc. where each contact can have any number of addresses.) The NUDC databases were designed before vCard and iCalendar formats came on the scene. (In fact, members of the NUDC development team served on the committees that helped design the precursors to those formats.) NightHawk has the advantage of being designed to share data with modern data formats, so the database schemas used in NightHawk are designed to be a superset of the AddressBook and iCal Sync Services schemas (which, in turn, are closely patterned after vCard and iCalendar). The result is that it is easy for NightHawk to sync to AddressBook and iCal and vCard and iCalendar.

As it turns out, it is far harder for NightHawk to read data from Now Contact and Now Up-to-Date files than it is to read data from vCard or iCalendar files. As a result, we only import data from Now Contact and Now Up-to-Date files into NightHawk.

By supporting the industry-standard data formats, there is a huge win for NightHawk:
  1. It's easy to sync to AddressBook and iCal
  2. It's easy to sync to PDAs and phones supported by SyncServices and other sync standards, such as SyncML
  3. We can leverage third party code to read and write vCard and iCalendar format files, so we're more likely to inter-operate with other products.
Database of the Database

One of the problems with NUDC in a group environment was that anyone could change anything. One day you'd have a meeting on your calendar and next day it would be gone because someone decided to delete it. Unlike NUDC, NightHawk contains a full history of every edit made on the data. If someone changes an entry, NightHawk knows who did it, when they did it, and what it used to read. You can then undo specific edits or collections of edits. This should go a long way to pinpointing changes in your calendar or contact file!!

Conclusion

NightHawk's database gives us a solid platform to build the rest of the application. It contains all of your data records, all of your preferences, and manages all of the syncing to the NightHawk server and to your mobile phones and PDAs. At its core, NightHawk contains a fast, flexible and advanced database architecture. And if I do my job right, that database will be humming smoothly and silently in the background. It'll just work.

If you have questions about our database or other parts of NightHawk, write to me at nighthawk@nowsoftware.com.

Thanks!
John

3 comments:

Wilson said...

It's a good move by going to SQL....

Hopefully, I'll be able to have my contact records with a special section for Relationships.

Then I can open up a contact for someone named Jack and see the other contacts linked to him such as....

Contact Name: John Doe

Relationships:
--------------
Spouse: Jane Doe
Son: Jack Doe
Daughter: Jill Doe



Then these relationship links can be a hyperlink to open up that person's contact entry (if there is one) in NightHawk....



That's the kind of stuff that I guess a SQL database could do that a flat file database wouldn't be able to do before.

I noticed that my Now Contact has duplicate entries in the Notes section. It appears that any tasks created in Now Up-To-Date are just duplicated in the Now Contact record when I link a task to a contact. But it's just copying data and not really offering a symbiotic link to that data. That's the folly of flat file databases.

I'm hoping that NightHawk would be able to get rid of these duplicate entries.

It's a mess for me in NUD/C 5. Sometimes I'll link a contact to a task and then watch Up-To-Date crash. When I check the contact record, double entries of the task I entered are placed in the Contact Notes section.

Very irritating at the moment. But hopefully NightHawk's SQL capabilities will help smooth everything over.

Maurice said...

As a Heavy PC user for 25 years and recent Apple convert I offer a new perspective on Contact managment software. Most Apple programs simply fail to grasp the user requireements of the business community. Act kind of gets it as do several windows programs. Apple software for business needs to focus on the core requirements for loyal apple users but even more so for the millions who would like to use Apple.

I would like to help you understand the mind and interface requirments of a Business user who has used just about every aspect of Act, Goldmine, etc.

It would be really exciting to see this product overtake these others and I don't think it would be difficult with a little thought.

Sorry I can not help on technical aspects but I think you have this aspect well covered

I look forward to offering some helpful perspectives for the interface aspect.

NightHawk said...

I appreciate ALL HELP!!! :) We're looking at setting up forum software so that folks can provide feedback and we can discuss what you want to see. I'll let you know when that is up and running.

Thanks!
John