Thoughts on manual database design?
cube1 at charter.net
Thu Sep 24 14:13:54 CDT 2015
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.
More information about the cctech