Thoughts on manual database design?

[Zane Healy]

My recommendation would be to ensure compatibility with the MARC database format.  Even if you don't include all the fields, the fields you do have should be compatible.  If you look you should find Open Source projects that are MARC compatible.  It's been several years since I looked into this, and then I was populating a MARC database from a massive Excel spreadsheet.

Zane



On Sep 22, 2015, at 7:00 PM, Jay Jaeger <cube1 at charter.net> wrote:

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