Database: INSERTS, speed and primary keys

[David Bovill]

I've spent the weekend refreshing on databases. I'm using LiveCode and also
Trevor's fabulous sqlYoga, and beginning to realise how little I know about
databases! The question I've got is about the database schema design and
optimising it for the speed of adding records.

* Exporting Handlers*
I think I may be missing some basic things, as it seems to me that the only
easy way to force an update to be unique is to use primary keys. You can use
"triggers" but this seems like this would be slower. However if I uses a
surrogate primary key (ie the usual auto-incremented numeric id field), for
each updated record I need to check first whether it is already in the
database before adding it - this is a lot of overhead in terms of finding
the ids based on other fields. However I ran in to problems using composite
primary keys, as the queries got a too complicated for me to track down the
bugs in them easily.

To elaborate on the example - in LiveCode each and every handler in the
LiveCode environment can be uniquely identified by the following fields:

   1. handler_name
   2. handler_type (ie one of "c,f,g,s" standing for "command", "function",
   "getprop", "setprop")
   3. location (the "rugged id" of the LiveCode control - ie control id 1387
   of card id 1002 of stack "Test")
   4. hander_num (not usually useful - but there can be more than one
   identical handler in a script, only "1" is ever called)
   5. handler_scope ("p" for "private" for example)

>From these elements you can define a unique reference to a given handler in
script with the following type of "key"

   - test_Command,c,stack "Test",1,p

Which would refer to (the firsts instance of) a private command
"test_Command" in script of the the stack "Test". I've been using this way
to refer to handlers for years and have a large library of code that
manipulates references to handlers, ie:

   - hkey_EditScript hKey
   - put hkey_GetHandler (hKey ) into somehandler
   - put hkey_ExtractHandlerCalls (hKey ) into hKey s

Therefore in database terminology hKey is a composite primary key, and you
can make this a composite primary key. This has the advantage of enforcing
that addition of keys to the database is normalized - that is adding an hKey
that already exists fails to add a duplicate entry.

The problem I am having is that while using a composite primary key is great
for making the updates fast and simple - it is giving me headaches with the
more complex joins and queries, I'm building in LiveCode / sqlYoga. However
I do need these updates to be as fast as possible - a few ticks for adding
several hundred records, as I want to automatically add them to the database
every time a script is compiled.

These are the options I can think of:

   1. Stick to composite primary keys for the handler table, and battle
   through all the bugs / issues with using complex joins based on composite
   keys.
   2. Use "triggers" - and unknown to me, not tested with regard to speed,
   and looks like it may be difficult to keep sqlite and online db in sync, as
   they may not share the same way of implementing "triggers".
   3. Use a "staging" table of some sort - use composite primary keys on
   this temporary table, and then at a later date (perhaps when the stack is
   saved) process this temporary table, moving the data into the db proper.
   4. Asynchronously update the db - perhaps using a slave process, to
   maintain GUI responsiveness.
   5. Something else?

At the moment I am going for 3, as it seems to offer the best in terms of
keeping the db design as conventional as possible, and speed in terms of GUI
response.

Any advice from the db experts out there?