Thoughts on manual database design?

[Jay Jaeger]

On 9/23/2015 8:18 PM, steven at malikoff.com wrote:

> 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.
> 
> CREATE TABLE Manual_Artifact
> (
> 	ArtifactID INT(11) NOT NULL AUTO_INCREMENT,
> 	. . . other fields . . .
> 	CONSTRAINT ArtifactID_pk PRIMARY KEY (ArtifactID)
> )
> 

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'll also need similar type primary keys on your other tables, and also set up the foreign key constraints for your db integrity if you really want to go
> that far for this project. Some of those tables could be coalesced to simplify the thing - as per the inane comment from the guy in Holland (or wherever)
> for instance the Location and Manual_Type tables.

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.

Those you mention and a few more FK constraints are there.  Some of the
tables, like Location and Manual_Type also exist to populate pull down
lists without having to read through larger tables to populate them

> 
> 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.

JRJ