24 Sep
2015
24 Sep
'15
12:13 p.m.
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