Thoughts on manual database design?

[Jay Jaeger]

So, I am looking to convert my old Access database I have used for many
years to a MySQL database, with the expectation that I will eventually
publish it on a web page for public lookup.

Below is my first cut at a database design for it.  I'd be happy for
comments and suggestions, to the list or directly.  Note, however, that
I don't expect to do this to the same level of complexity and
completeness that one might if they were the library of congress, a
major university library, etc.  So, some suggestions to generalize may
be quietly ignored, even if they would in principle, be the "right
thing" according to some criteria or other.

This would be used for manuals, per se, print sets, etc.  Quite possibly
for books as well.

What follows amounts to a data dictionary:

Table MANUAL:

  This is the primary table of manuals.  Many of the fields will be
  available for string and/or pull down search, as appropriate.

Machine_MFG:  VARCHAR(32)
  The manufacturer of the machine(s) to which the manual applies
  Part of unique key.
  Pull down search.

Manual_Number: VARCHAR(40)
  The manual number, including version strings, etc.
  In some cases, this may be an SBN or ISBN.
  Part of unique key
  String search.

Artifact_ID: CHAR(16) [format/type still under consideration]
  The local identifier for the manual/artifact.
  Part of unique key.
  Will be generated if none is entered during creation/update.
  (Required because I may have more than one copy of a given manual).

Publisher: VARCHAR(32)
  The publisher of the manual.  (Typically will be the same as the
  Machine MFG - but not necessarily always)
  May not be NULL.

Type: VARCHAR(16)
  Manual, Drawing (== printset, schematic, etc.), Book, ...
  Pull down search.

Original: BOOLEAN
  True if an original manual.  False if a copy ("Xerox").

Missing: BOOLEAN
  True if the manual is missing (i.e., is not where it is supposed to
be, and the actual location is not known).

Title: VARCHAR(255)
  The title of the manual, including any subtitles.
  Suggest "; " to separate multiple titles/subtitles
  May not be NULL
  String search.

Incomplete: CHAR(1)
  Indicates if the manual is not a complete copy.
  NULL indicates the copy is complete.

Year: DATE
  The year the manual was published / printed / copyrighted.
  May be NULL, signifying the date is unknown / not entered.

Location: VARCHAR(20)
  The general location of the manual in my inventory
  May be NULL, signifying the manual is not in my inventory.

Cabinet: CHAR(2)
  The ID of the cabinet or box in which the manual is stored, if any.
  May be NULL, even if the manual is in my inventory.
  May not be NULL if Drawer is not NULL.

Drawer: CHAR(2)
  The ID of the drawer of the cabinet in which the manual is stored,
  if any.  May be NULL.

Inventory_Date:  DATE
  The date that the manual was last inventoried.

Filename: VARCHAR(255)
  The name of the file which is an image of the manual in my local
  file storage.

OnlineLocation: VARCHAR(255)
  URL of the manual located online (e.g., the bitsavers URL)
  May be NULL.  This does NOT mean that the manual is not online.
  Of course, this field can become out of date.

OnlineVersion: CHAR(1)
  = means that the online manual is the same version as this one
  > means that there is no equal version online, but there is a
    later version
  < means there is no equal or later version online, but there is an
    earlier version.

OnlineMD5: CHAR(32)
  MD5 hash of online manual - used to check for moved manuals.  ;)


TABLE MachineManual

This table cross references manuals to the machine(s) to which they
apply.  This list will NOT be guaranteed to be complete.  During inquiry
and update, this will be a list of machines with a separator character
in between which will not be allowed in Machine_MFG.  During an update,
the entire list will be replaced with the first one being flagged as the
primary machine.

MachineMFG:  (See above).  Part of the unique key for this table.
Manual_Number: (See above)  Part of the unique key for this table.
Machine:  VARCHAR(16)
   One of the machine(s) to which this manual applies.
   Part of the unique key for this table.
Primary_Machine: BOOLEAN
   When true, indicates that this machine should be listed first
   in the list of applicable machines.  For a peripheral, this will
   typically be the machine for which the peripheral was originally
   designed.

So a list of machines for an RX01 manual might be:
   PDP-11, RX01, RX01, VAX-11


TABLE Machines

A lookup table of valid machines.  Only column is Machine (See above).
Naturally this table will change over time - I anticipate that an update
where the updater (me) keys in an "invalid" one will be greeted with
some kind of confirmation panel.


TABLE Manufacturers

A lookup table of valid manufacturers.  Only column is Machine_MFG
(See above).  Naturally this table will change over time - I anticipate
that an update where the updater (me) keys in an "invalid" one will be
greeted with some kind of confirmation panel.


Table Types

A lookup table of valid Types. Only column is Type (See above)

TABLE:  LastGeneratedArtifact

The last generated artifact ID - used to assign new artifact numbers
when one is not manually entered.  Algorithm TBD.  One column:
Artifact_ID (See above)


TABLE: Locations

A table of valid locations (e.g. Basement, Garage, STORAGE, Unknown, etc.)
Used for convenience during data entry.


(Once this one is done and running (at least locally), the next one is
probably media (tapes, floppies, etc.) which is not too bad, and then
machines, cards and parts, which is more complicated for lots of reasons).

After those are done (hopefully by the end of the year - I am retired
and this will be my primary focus for a while) comes the design for a
database for SMS cards and for the IBM 1410 use of SMS cards.


JRJ