On 9/23/2015 1:38 AM, ste...@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
Slightly more useful than MARC, but really it comes down to just a field naming issue. Relatively few of my fields are present there: Title, Publisher, Creator (what I called Machine_MFG), Date (though their definition is really really vague), identifier (my Manual_Number), Their type would just be "Text" or "StillImage" for all of my stuff - not sufficiently specific for my uses. Subject is only *roughly* equivalent to my Machine table, and would not do the job I need done with it. I don't plan on a lengthy description. No way I am going to enter that much data. > > 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. > I certainly understand that point of view. However, that really isn't an issue for me, and MySQL is presumably better supported by hosting outfits. > 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 :) Probably. On the other hand, really easy to make them longer, too, should that prove necessary - this isn't a 100 million row table. And the fields are already longer, in most cases, than I would want to actually display and longer than the fields I currently have - which have already been lengthened as needed. > Final thing, are you going for a completely normalised database? (ok, I > haven't analysed your schema to any great extent) THANKS FOR ASKING THAT QUESTION. Well I thought I had done a decent job of practical normalization, but clearly missed some things that I noticed as a result of your question. First, I certainly should pull out the File and Online fields into a separate table because as is they could result in redundancy/inconsistency between multiple copies in my inventory. Pulling them into a separate table will remove the redundancy, and also allow for the eventual possibility of multiple online sources for a given manual. TABLE File Fields: Machine_MFG(K), Manual_Number(K), OnlineLocation(K)*, OnlineVersion*, OnlineMD5*. (Filename goes away - it is just an instance of "OnlineLocation") (*) - removed from original Manual table. Theoretically, I should separate the copies into a separate copy table, keyed by artifact ID, but it wouldn't really buy me much, as I almost always store multiple copies of a manual in the same place. Still it probably would be the way to go: TABLE Artifact Fields: ArtifactID(K), Location*, Cabinet*, Drawer*, InventoryDate*, Missing*, Original*, Incomplete*, Publisher*, Date* I think I will add a field "Marked" - Boolean - true if an artifact has been marked with its ArtifactID. I *might* also yank Publisher and Date into this table. Have to think about that, but with the possibility of multiple printings, the Date almost certainly belongs here. I suppose one could argue the same for Publisher. AH YES! HP and Apollo - different publishers for the exact same manual. So, yes, these need to move too. So now, table Manual just has the following: Machine_MFG(K), Manual_Number(K), Title, Type And I need a relationship table to bind the two together: TABLE ManaulArtifacts ArtifactID(K), Machine_MFG, Manual_Number > > Steve. > > > ---------------------------- Original Message ---------------------------- > Subject: Thoughts on manual database design? > From: "Jay Jaeger" <cu...@charter.net> > Date: Wed, September 23, 2015 12:00 pm > To: cctalk@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 >> >> >> >> >> >> >> >> >> >> > > >
