Thoughts on manual database design?

[mark at markesystems.com]

>> Not sure why you have VARCHARs for primary keys, why not use the
>> conventional auto-increment int so you can dispense with
>> the LastGeneratedArtifactID table.
>>

> Because my artifact ID's are not always just numbers.  In some cases
> they may already be marked on an artifact (though typically not for
> manuals - but this is just the first of a set of such projects, and they
> *are* marked on many of my computer boards).

You can still force the artifact ID VARCHARS to be unique, and index them as
well, of course.  There are at least a couple of reasons to have primary
keys that are independent of the "visible" key.  First, if the user-visible
key ever changes (what if the inventory tag falls off and is lost?), that
will break all the links that refer to that record (or else you'll need
extra code to handle this).  Also, there have been many times when some
aspect of a key that's directly tied to an external bit of information needs
to change format - numeric to character, or length change, or...  This too
will break things.

> No, I don't need made up primary keys.  The other tables have the keys
> they need to guarantee uniqueness - in some cases the PK is made of up
> two or more columns.  I seriously dislike the current fad of inventing
> such keys when they are not needed.

I too used to develop new databases this way, figuring that since a certain
bit of information is guaranteed to be unique (or that I want to guarantee
its uniqueness), I'd use that for the primary key.  After getting bitten
more times than not, I now almost always create an auto-number key whose
only purpose may be for internal linkage.

(I recently developed a project using Zoho Creator, which was a learning
experience to say the least.  It's worth noting that an explicit ID field is
part of *every* data table that you can create there - there's no way around
it.  And, it turned out that it was pretty darn helpful a lot of the time,
too.)

>> Another thing, although MySQL is fine but for this I think SQLite might 
>> be a better choice of db. Its access methods are all in-process ie. no 
>> external
>> dbms service to bother with, just a library to link in and the physical 
>> database is a disk file (.s3db extension). It has a much 'lighter' db 
>> footprint.
>>
> As I mentioned in another response, I truly dislike SQLite, based on my
> experience with it on my Garmin GPS.

I'm still not sure why - my experience has been very good.  What bad 
experiences have you had?
~~
Mark Moulding