Thoughts on manual database design?

Wed Sep 23 02:41:31 CDT 2015

BIKESHED ALERT! BIKESHED ALERT!!

On 23-09-15 08:38, steven at malikoff.com wrote:
> Instead of cooking up another completely custom inventory management schema, you might consider adhering to Dublin Core for some more modern
> schema conventions:
> https://en.wikipedia.org/wiki/Dublin_Core
>
> For the dbms engine I would use MariaDB over MySql (but with some of the MySQL tool chain) as I prefer code to remain outside the clutches of Oracle:
> https://mariadb.org
> It's a drop-in replacement, runs nicely and the HeidiSQL management client is very useable.
>
> Also, why are you restricting your field lengths so much? This is the 21st century, you can afford to be a bit more generous on those VARCHARs :)
> Final thing, are you going for a completely normalised database? (ok, I haven't analysed your schema to any great extent)
>
> Steve.
>
>
> ---------------------------- Original Message ----------------------------
> Subject: Thoughts on manual database design?
> From:    "Jay Jaeger" <cube1 at charter.net>
> Date:    Wed, September 23, 2015 12:00 pm
> To:      cctalk at classiccmp.org
> --------------------------------------------------------------------------
>
>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>

-- 
Met vriendelijke Groet,

Simon Claessen
drukknop.nl