Thoughts on manual database design?
steven at malikoff.com
steven at malikoff.com
Wed Sep 23 01:38:04 CDT 2015
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
>
>
>
>
>
>
>
>
>
>
More information about the cctalk
mailing list