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" <[email protected]> Date: Wed, September 23, 2015 12:00 pm To: [email protected] -------------------------------------------------------------------------- > 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 > > > > > > > > > >
